|
I am a beginner to database design and was wondering about primary keys and clustered indexes in tables. It seems like it is common advice to have a primary key for every table, which makes sense to me. However in SQL Server Management Studio a primary key automatically creates a clustered index. In reading the book I ran across this passage: "The strength of index-organized tables and clustered indexes is mostly limited to tables that do not need a second index." But when reading around if a PK should be clustered it seems like people generally agree that it should: "With few exceptions, every table should have a clustered index defined on the [PK]" -http://msdn.microsoft.com/en-us/library/ms190639(v=sql.105).aspx The logic in the book seems pretty convincing, but after reading that in Books Online I'm confused. I use SQL Server 2008 R2 primarily -- is there a reason that they would recommend to almost always use clustered indexes? Most of the tables I have require more than one index, so it would seem to me like a clustered index would not be good. Thanks! Asa asked Jun 12 '13 at 23:23 
asaorg |
|
I can fully understand your confusion. I think there is mostly a "religious" kind of disagreement between Oracle and SQL Server folks. In SQL Server, clustered indexes have always been proposed as something good, something which is an "advanced feature". That's probably the reason it is the default in SQL Server and why there are so many statements that they are a great thing. However, the clustered index hype in SQL Server is not entirely "religious", there are technical reasons too. This becomes most striking when looking at SQL Azure (the cloud SQL Server) which technically must have a clustered index on every tabel (there is no choice). Obviously, as you have correctly understood from my book, I'm not a "blind" advocate of clustered indexes. They do have their place is special cases, but they are also causing troubles in many other cases. The only reason why I don't strictly advice against using clustered indexes in SQL Server is some features require clustered indexes (e.g., partitions) and I'm not sure if the next SQL Server release still supports heap tables in future releases. So, in SQL Server a clustered index (not necessarily on the primary key!) is probably the way to go in most cases :( Edit: I've recently blogged about that too: Unreasonable Defaults: Primary Key as Clustering Key answered Jun 13 '13 at 06:33 
Markus Winand ♦♦ Thanks Markus! Out of interest -- do you know the technical reasons they are recommended?
(Jun 14 '13 at 20:56)
asaorg
|