Is it right way to add filtered column as main column for creating filtered index

• dudekula.kareemulla

  Ten Centuries

  Points: 1039

  More actions

  December 9, 2016 at 4:59 am

  #316624

  Hi

  I have a query like below. table contains 70 million records

  select MIN(createtime) _time_ from tablename

  where status <> 20 and type_id = 44

  and createtime > GETDATE() -3

  I want to create index like below

  CREATE NONCLUSTERED INDEX indexname ON table_name

  ( status, type_id , createtime) WHERE status<>(20)

  since status column is a filter condition is it good way to add status column as main column list also for the index.

  Can any one suggest me how it will make difference.

• DesNorton

  SSC-Insane

  Points: 24279

  More actions

  December 9, 2016 at 5:27 am

  #1917021

  dudekula.kareemulla (12/9/2016)

  ---

  Hi

  I have a query like below. table contains 70 million records

  select MIN(createtime) _time_ from tablename

  where status <> 20 and type_id = 44

  and createtime > GETDATE() -3

  I want to create index like below

  CREATE NONCLUSTERED INDEX indexname ON table_name

  ( status, type_id , createtime) WHERE status<>(20)

  since status column is a filter condition is it good way to add status column as main column list also for the index.

  Can any one suggest me how it will make difference.

  type-id is an equality filter, so I would add it as the leading edge of the index.

  status is simply a filter, and not used in the sample query, so I would exclude it.

  CREATE NONCLUSTERED INDEX indexname

  ON table_name ( type_id, createtime )

  WHERE status <> 20

  Then again how many statuses are there? It may be beneficial to not filter the index and just use

  CREATE NONCLUSTERED INDEX indexname

  ON table_name ( type_id, createtime, status )

  ---

  How to post data/code on a forum to get the best help.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 11, 2016 at 2:15 pm

  #1917287

  DesNorton (12/9/2016)

  ---

  Then again how many statuses are there? It may be beneficial to not filter the index and just use

  CREATE NONCLUSTERED INDEX indexname

  ON table_name ( type_id, createtime, status )

  I'd go with this, unless status 20 is 90% or so of the table. Filtered indexes get a little finicky sometimes.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass