Which index will get the prority and why?

Question

Which index will get the prority and why?

forsqlserver

SSCoach

Points: 18902
More actions
May 10, 2011 at 3:36 am

#239334

I am able to create Cluster and Non clustered index on same Column of same table.
Which index will get the prority and why?
create index IX__table_1 on table_1(a)
create clustered index IXcluster__table_1 on table_1(a)
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Depends completely on the query.

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

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 2

Note it when you create the Cluster index then non-clustered index on the same table will be drop and then recreate autmatically

It depends on the query you have provide if you create first non-clustered index then non-clustered index will be create first but then you create a clustered index on the same table then non-clustered indexes will be drop and recreate after clustered index created

My Blog
www.aureus-salah.com

Lowell SSC Guru Points: 323518 More actions · Answer 3

I'd also add than if a WHERE statement is included in the command, that is used by the optimizer to decide which index will be used to retrieve the data.

If an index exists on say, the LastName column, and you have a WHERE statement like WHERE LastName = 'Shaw', that index would most likely be sued(you need to see the execution plan to be sure.

if you search on a column that does not have an index, or use a non-SARG-able argument,then a table scan will occur, once again based on whatever index the optimizer thinks is best to look for the data with...most likely the clustered index

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!