difference between composite index and single index

Question

difference between composite index and single index

ajaysanhotra

SSC-Addicted

Points: 468
More actions
August 20, 2011 at 9:54 am

#99413

Hi i am curious to know what is the difference between
CREATE INDEX PIndex ON Persons (LastName) ,
CREATE INDEX PIndex ON Persons (DOB)
AND
CREATE INDEX PIndex ON Persons (LastName,DOB)

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

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

ajaysanhotra (8/20/2011)
Hi i am curious to know what is the difference between
CREATE INDEX PIndex ON Persons (LastName) ,
CREATE INDEX PIndex ON Persons (DOB)
AND
CREATE INDEX PIndex ON Persons (LastName,DOB)

You can't create an index with the same name, so the second statement above will fail.

But, you're asking what's the difference between the two? The first two statement create two different indexes consisting of a single column each. The second creates a compound index with two columns. This does a few things. First, the selectivity (uniqueness) of the second index will be much higher because it is the combination of both sets of values that make up the selectivity. Second, it really depends on what type of queries are being run. With the first set of indexes, if you have a query that is selecting on either value, then SQL Server can make use of the appropriate index, but if your query is selecting a combination of values, unless you hit the rare instance where SQL Server will do an index JOIN, then it's likely to do a scan on one index or the other and then a key lookup or possibly it'll just skip over & do a clustered scan.

In the third index, if you're getting the combined query with the AND statement, then this index is likely to be used, but, if DOB is passed in alone, the index won't be used. But if the LastName is passed in it will be because LastName is the leading edge of the index and the field that SQL Server will use to determine if the index might be helpful.

There's more to it, but that's it in a nutshell.

"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

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

http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/

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

ajaysanhotra SSC-Addicted Points: 468 More actions · Answer 3

thanx for the valuable suggestion,

but i have one question

should the sequence of which i give columns name matters

create index pindex on (id,Full_Name,quali)

and can i have a single index on Full_Name and also composite index include the Full_Name column

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

The choice of which one comes first does matter. If you were to have a composite with the leading edge of FamilyId and a single index with the leading edge of FamilyId, the singleton would be extraneous. But if you had the other column on the leading edge and had FamilyID as a second key in that index, that's fine.

Also, don't forget about the INCLUDE statement that lets you add columns at the leaf level of the index without affecting the key. It offers another mechanism for creating a covering index.

"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

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

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

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

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 6

Gail's indexing articles are really good, by the way. I've been using indexes for, er, decades (am I that old? Eeep!) and I still got something out of them. She's posted the links to the articles, and I can recommend reading them.