SQL Index Help..

• rocky_498

  SSCertifiable

  Points: 6532

  More actions

  January 21, 2013 at 5:12 pm

  #269563

  Hi guys,

  I have question for you guys,

  I have one table ABC (For Example) and Table ABC has few Indexes

  First Index on Table ABC

  COLUMN_NAME = ABC.COLUMN1

  COLUMN_NAME = ABC.COLUMN2

  Second Index

  Column_Name = ABC.COlumn1

  My question is Would it be same. I can understand if i have Query with both column COlumn1 & Column2

  then First Index is good. What you think should i delete Second Index or Keep, if i keep second

  index its not gonna slow down the performance?

  Please guide me.

  Thank You

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  January 21, 2013 at 5:14 pm

  #1579417

  Since indexes are Left Based, the second index is a duplicate.

  The only reason to not remove it is if somebody is using an index hint with the name of this second index.

  Removing it should have no negative impact. But really, you should test in your dev/qa environments first.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• Feivel

  SSCertifiable

  Points: 7524

  More actions

  January 22, 2013 at 5:11 am

  #1579567

  Unless your users have specific interest in using that index as an hint, thereby forcing the optimizer to think that you're smart enough to trick it. Apart from this I don't find any use of that index, as it's just a duplicate index lying there, and occupying unnecessary space. I would usually let the optimizer do that work for me rather than forcing it to choose something else which might no be the best alternative, and could impact performance.

  Regards,

  Faisal

• ScottPletcher

  SSC Guru

  Points: 101093

  More actions

  January 24, 2013 at 9:33 am

  #1580601

  IF and only if ABC.COLUMN2 is a (very) large column, and ABC.COLUMN1 is used by itself a lot, it might be worthwhile to keep both indexes.

  You should also review sys.dm_db_index_usage_stats to see how often, and in what ways, each index is currently being used.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".