NonClustered index vs Unique NonClustered Index

Question

NonClustered index vs Unique NonClustered Index

SQL*

SSCertifiable

Points: 6638
More actions
July 27, 2012 at 2:53 am

#264894

Hi All,
What is the main difference between Nonclustered index and unique nonclustered index with respect to the internal storage?
I have made a test to know the difference, and i found that the query is using Unique Index where as non unique index is not used by the query.
Can any one tell me why this difference.
Kindly check the attached execution plan of the both queries.
Thanks,
🙂

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

SQL* (7/27/2012)
What is the main difference between Nonclustered index and unique nonclustered index with respect to the internal storage?

Nothing significant.

The only difference (other than that one is enforced unique and one isn't) is that the clustered index key is part of the include columns when the nonclustered is unique, part of the key columns when it's not.

I have made a test to know the difference, and i found that the query is using Unique Index where as non unique index is not used by the query.
Can any one tell me why this difference.
Kindly check the attached execution plan of the both queries.

If you'd attached the execution plans, I would. Pictures of the execution plan are near-useless.

The query and index definitions would help.

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

SQL* SSCertifiable Points: 6638 More actions · Answer 2

Thank you,

But, for your information i have only created NON Clustered index not clustered index.

If you observe the attached images, i have mentioned the index definition/query/execution plans.

1.Unique Index only using the Created Non Clustered Index (I have not created any clustered indexes).

2.Non Unique index is not using the index.

🙂

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 3

EDIT: You misread Gail's first sentence. Go back and re-read. She refers to both Clustered and Non-Clustered Unique indexing properties.

As Gail said, the images are useless for us. You need to include the actual execution plans.

Click the Execution Plan tab, then go to the File menu. Look for "Save Execution Plan As" and choose that option. Save the results on your computer, then attach them to the thread in your next post.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

SQL* SSCertifiable Points: 6638 More actions · Answer 4

I followed the same steps, but that is also giving the same result,

what i have attached is, i have taken print screen of the ssms window after executing the query so that it contains both query and execution plan?

Am i missing something?

Thanks,

🙂

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

SQL* (7/27/2012)
Am i missing something?

That pictures are useless and we would like to see the execution plans please. Brandie explained how to save them

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

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

SQL* (7/27/2012)
Thank you,
But, for your information i have only created NON Clustered index not clustered index.

Replace 'clustered index key' with 'RID' and everything I said will still be completely valid. I wasn't talking about how you create the clustered index. I was talking solely about the nonclustered index

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

SQL* SSCertifiable Points: 6638 More actions · Answer 7

please find attached execution plan files.

Please let me know the reason.

🙂

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

Your index is not covering and you've tested with an incredibly small number of rows.

The unique index tells the optimiser that it will do one and only one lookup from the heap, so it knows that it's safe to do a key lookup. The non-unique doesn't give that and the seek + key lookup requires that a very small percentage of the table be returned by the query, the number of rows in the table is too small for that to be true.

You need large numbers of rows to do tests like this, not 5 or 10.

At very small row counts, the table scan will be faster (one read vs at least 2 for the index seek + lookup)

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

SQL* SSCertifiable Points: 6638 More actions · Answer 9

Thank you,

I am not able to understand your point. can you explain little more?

Do you have any references on this?

Thanks,

🙂

SQL* SSCertifiable Points: 6638 More actions · Answer 10

Hi Gail,

i have posted one more topic on indexes on that same day.

I believe you know very well,

http://www.sqlservercentral.com/Forums/Topic1336331-391-1.aspx%5B/url%5D

can you explain the things please.

🙂

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

SQL* (7/30/2012)
I am not able to understand your point. can you explain little more?

Explain what exactly?

Do you have any references on this?

Google will. Tipping point, key lookups.

Also

http://www.sqlservercentral.com/articles/Indexing/68439/

http://www.sqlservercentral.com/articles/Indexing/68563/

http://www.sqlservercentral.com/articles/Indexing/68636/

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