Can we Prioritize which index to be used first in a Select query ?

• balajisrm

  Right there with Babe

  Points: 723

  More actions

  September 15, 2012 at 9:31 am

  #262008

  Can we Prioritize which index to be used first in a Select query? if so how can this be done.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  September 15, 2012 at 9:59 am

  #1538282

  Do not know if this will do exactly what you want to do, but read here:

  http://msdn.microsoft.com/en-us/library/ms181714(v=sql.100).aspx

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 15, 2012 at 10:30 am

  #1538286

  The optimiser chooses the best index for a query based on its knowledge of the data distribution, rows involved and a few other factors.

  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

• MysteryJimbo

  SSC-Insane

  Points: 24203

  More actions

  September 16, 2012 at 2:22 am

  #1538321

  You can but I would only advise this in exceptional circumstances like you are not in a position to updates stats on a very, very large table or in a high tps environment. In a sense "dont try this at home" unless under supervision of an adult or an experienced dba.

  Adding WITH(INDEX=idx_i_am_an_index) just after the table name or alias.

  Another alternative is adding OPTION(FORCE ORDER) enforces the join order of the query given.

• balajisrm

  Right there with Babe

  Points: 723

  More actions

  September 16, 2012 at 5:23 am

  #1538328

  Thanks for your help!