StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The <strong>primary key</strong> is a logical concept in relational database theory - it's a key (and typically also an index) which is designed to uniquely identify any of your rows. Therefore it must be unique and it cannot be NULL.</p> <p>The <strong>clustering key</strong> is a storage-physical concept of SQL Server specifically. It's a special index that isn't just used for lookups etc., but also defines the physical structure of your data in your table. In a printed phonebook in Western European culture (except maybe for Iceland ), the clustered index would be "LastName, FirstName".</p> <p>Since the clustering index defines your physical data layout, you can only ever have one of those (or none - not recommended, though).</p> <p>Requirements for a clustering key are:</p> <ul> <li>must be unique (if not, SQL Server will add a 4-byte "uniqueifier")</li> <li>should be stable (never changing)</li> <li>should be as small as possible (INT is best)</li> <li>should be ever-increasing (think: IDENTITY)</li> </ul> <p>SQL Server makes your primary key the clustering key by default - but you can change that if you need to. Also, mind you: the columns that make up the clustering key will be added to each and every entry of each and every non-clustered index on your table - so you want to keep your clustering key as small as possible. This is because the clustering key will be used to do the "bookmark lookup" - if you found an entry in a non-clustered index (e.g. a person by their social security number) and now you need to grab the entire row of data to get more details, you need to do a lookup, and for this, the clustering key is used. </p> <p>There's a great debate about what makes a good or useful clustering and/or primary key - here's a few excellent blog posts to read about this:</p> <ul> <li><a href="http://www.sqlskills.com/BLOGS/KIMBERLY/category/Indexes.aspx" rel="nofollow noreferrer">all of Kimberly Tripp's Indexing blog posts are a must-read</a></li> <li><a href="http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx" rel="nofollow noreferrer">GUIDs as primary key and/or clustering key</a></li> <li><a href="http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Continues.aspx" rel="nofollow noreferrer">The Clustered index debate continues....</a></li> </ul> <p>Marc</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload