December 30, 2015 at 12:24 pm
I saw two indexes create options. What are the difference between them and in what case should we use either option 1 or option2 (below)? please don't refer me to a book or article I just want to understand it in plain (simple) term.
option 1.
CREATE NONCLUSTERED INDEX ix_personName
ON Person.Person(LastName, FirstName, MiddleName)
option 2.
CREATE NONCLUSTERED INDEX ix_personName
ON Person.Person(LastName, FirstName)
INCLUDE (MiddleName)
December 30, 2015 at 1:34 pm
For option 2, MiddleName is not a key column in the index, it's only on a leaf level, therefore a server cannot perform an index seek on that column but an index becomes smaller and requires less IO to read it. By adding all the necessary columns for a particular query into the INCLUDE clause you can eliminate an expensive lookup operation.
December 30, 2015 at 2:38 pm
Alexander Suprun (12/30/2015)
For option 2, MiddleName is not a key column in the index, it's only on a leaf level, therefore a server cannot perform an index seek on that column but an index becomes smaller and requires less IO to read it. By adding all the necessary columns for a particular query into the INCLUDE clause you can eliminate an expensive lookup operation.
Just be careful when adding included columns. The more you add the more overhead that may be incurred when data is updated. Also it increases the size of the database.
December 31, 2015 at 10:38 am
Thanks Alexander and Lynn. One more question, which option is better? if option 2 is better then why not add firstName and Middlename in Include?
December 31, 2015 at 10:56 am
Tac11 (12/31/2015)
One more question, which option is better?
What is better a car or a boat? 🙂
if option 2 is better then why not add firstName and Middlename in Include?
Seriously?
For option 2, MiddleName is not a key column in the index, it's only on a leaf level, therefore a server cannot perform an index seek on that column but an index becomes smaller and requires less IO to read it. By adding all the necessary columns for a particular query into the INCLUDE clause you can eliminate an expensive lookup operation.
December 31, 2015 at 11:14 am
Tac11 (12/31/2015)
Thanks Alexander and Lynn. One more question, which option is better? if option 2 is better then why not add firstName and Middlename in Include?
Then what are you building the index on?
December 31, 2015 at 11:19 am
Tac11 (12/31/2015)
One more question, which option is better?
Both. Neither.
As with all index-related questions, it completely depends on the queries running against the table. If you have queries that filter on all of LastName, FirstName, MiddleName, then option 1 is better. It you only have queries that filter on LastName, FirstName, then option 2 is better.
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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply