making my index create a bit less dynamic

db042190 1,516 Reputation points
2022-04-18T13:13:53.237+00:00

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
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,576 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-04-18T17:36:16.247+00:00

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

    0 comments No comments

  2. Erland Sommarskog 103.5K Reputation points MVP
    2022-04-18T18:53:45.757+00:00

    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.

    0 comments No comments

  3. Bert Zhou-msft 3,421 Reputation points
    2022-04-19T02:40:44.04+00:00

    Hi,@db042190

    Welcome to Microsoft T-SQL Q&A Forum!

    ->>![194038-image.png][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.

    0 comments No comments

  4. db042190 1,516 Reputation points
    2022-04-21T18:16:24.82+00:00

    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.