making my index create a bit less dynamic

Question

Hi we run 2014 enterprise. I have a dynamic query that generates "filtered index" creates based on the initial max value of a certain column. The idea is that we would be wasting space if we built the index over (sometimes 60 years) all of history. Basically the query uses a cte that gathers the max value of that column from 20 tables and the cte is joined with a query sourced from the information_schema.columns view.

our dba feels that this is too dynamic for db projects. We use db projects to source almost everything (procs, table creates, synonyms, indexes etc) except ssis packages and ssrs rdl's.

so before i spoke with him , i tried what you see below but of course it didnt work. Is there a way to make a filtered index create less dynamic so i can make my dba happy?

USE [research]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable2](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [TestColumn] [decimal](19, 4) NULL,
 CONSTRAINT [PK_TestTable2] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert testtable2(testcolumn) select 9
declare @trythis decimal(19,4) 
select @trythis=max(testcolumn) from testtable2

create index ix1 on testtable2 (testcolumn) where testcolumn > @trythis

Answer 1

You are likely better off simply indexing testcolumn and removing the filter.

Answer 2

I'm not sure that I get this. So the idea is that the index will cover all data that is entered in the future, but not the data that is there now? How much space to expect to save? You talk about data for 60 year. That gives me the feeling that it is a miniscule you are saving.

So I'm inclined to agree with Tom that you are better off without the filter.

Answer 3

Hi，@db042190

Welcome to Microsoft T-SQL Q&A Forum!

->>][2]

Do you think this is where dynamic indexing is implemented? The experts also recommend that you delete the WHERE filter, But I keep my perspective. To save space, you'd have to find the maximum value of a column in TestTable2 every time, write a function to determine if that column is the maximum value, and create an index based on that value.
Maybe this is just convenient for your DBA, but it will be more tedious to maintain the index later.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

thx Erland and Bert.

Erland, you got it. Its across 20 tables and it is a significant amount of space for this company. I don't think it would be significant anywhere else. If it was my decision, I'd do it your way.

Bert, i'm not sure what you mean by dynamic indexing but i recommended to my dba that he redefine these once a year. And i think the dynamic script is doing what you say, ie grabbing the high value and generating a filtered index create.