You are likely better off simply indexing testcolumn and removing the filter.
making my index create a bit less dynamic
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
Sign in to comment
Sort by: Most helpful
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.
Welcome to Microsoft T-SQL Q&A Forum!
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.
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.
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.
Your filtered index would never be used unless you search by WHERE testcolumn > xxxx. Is that how you use this table?
And note that this includes a condition like:
testcolumn > @value
where @value has a value over the limit. Unless you have OPTION(RECOMPILE), the optimizer cannot make the assumption that the variable will always be above the index filter.
And I still have some difficult to understand the point with setting an index filter with values above the current max value. Or this related to this timestamp thing that I recall that you asked about some time ago?
Sign in to comment