Index question

Question

Index question

djj

SSCoach

Points: 18831
More actions
February 11, 2014 at 7:42 am

#303962

Hello,
If I create an index
CREATE INDEX IX_myindex1 ON mytable (ColumnA, ColumnB);
Do I/should I need to create a second index?
CREATE INDEX IX_myindex2 ON mytable (ColumnA);
Thank you,
djj

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323507 More actions · Answer 1

no; since the leading edge of the first index has the same column, you don't need another index;

if there are WHERE statments on ColumnB only, then an idnex on just that column might make sense, but it depends on how unique the column is as to whether the optimizer might use the index vs just scannign the table.

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!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

It depends.

What do you plan these indexes will be used for? Are you going to use these columns in JOINS, sometimes one sometimes two? SELECTS?

There is nothing wrong in having both indexes if you really need them.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

djj SSCoach Points: 18831 More actions · Answer 3

Thank you. I had thought that was the case, thus the question.

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 4

It's extremely unlikely that the second index will get used. It's possible it will get used if the optimizer determines that it needs to do a scan of the entire index only because the index will be a little smaller since it only has a single key. But, that seems a little bit of an edge case since the histogram within the statistics for both indexes will be identical.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

please note, the the following is a bit different setup...

CREATE INDEX IX_myindex1 ON mytable (ColumnA, ColumnB);

CREATE INDEX IX_myindex2 ON mytable (ColumnB);

As, Grant mentioned, the Index(A,B) and Index(A) will have identical histogram in the statistics.

Index(A,B) and Index(B) pair is a more common scenario, as it gives quite different picture.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

djj SSCoach Points: 18831 More actions · Answer 6

Thank you all.

I knew that ColumnB would need its own if it required an index, I just wanted clarification on the first column in an index list. But it bears repeating for those who may not know.