when to use clustered vs nonclustered indexes

Question

when to use clustered vs nonclustered indexes

mtl20144102

Old Hand

Points: 334
More actions
November 30, 2018 at 8:38 am

#373177

When should you use clustered vs non-clustered indexes? Which is better for large tables?

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

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

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

This is a long and involved topic. I'll try to keep it short and focused. A clustered index is not simply an index. It also defines the storage of the data. Every table (with exceedingly few exceptions) should have a clustered index. SQL Server is optimized around having a clustered index define the storage of the tables. So, every table ought to have one. Clustered indexes have to be unique. However, they don't have to be the primary key for the table. I generally recommend making the clustered index that column (or columns) that are most frequently used for data retrieval. For some tables, that's the primary key. For others, it's something else.

Then, you have nonclustered indexes. These are for additional filtering done by secondary queries that give you additional ways to speed data access. You would use these to add to a table that already has a clustered index.

There's tons and tons more to these topics. I'd strongly suggest you get a copy of my book which has all those details.

"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

x SSC-Insane Points: 23660 More actions · Answer 2

Clustered indexes have to be unique.

This doesn't seem to be enforced by SQL Server. Just sayin!

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

A clustered index must be unique, but not necessarily in the columns you use to define it. For a non-unique clustered index, SQL Server will automatically add a hidden four-byte column to make it unique.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Scott Coleman - Tuesday, December 4, 2018 3:37 PM
A clustered index must be unique, but not necessarily in the columns you use to define it. For a non-unique clustered index, SQL Server will automatically add a hidden four-byte column to make it unique.

From what I understand about the "uniquifier" that you speak of, it is only present on rows that are duplicate keys. And uniquiness is only measured in the keys defined in the index.

Please see the following for one of the best videos ever on indexes.
https://www.youtube.com/watch?v=QjCEkI8Qm5c

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)