At Stack Overflow the other day, I once again found myself trying to debunk a lot of the wisdom” in the SQL Server community. You can find the “revealed post here: to read the discussion. However, this post is not about GUID or sequential keys, which I have written about , it is about cluster indexes and the love affair that SQL Server DBAs seem to have with them. It is interesting to note that both SQL Server and MySQL by default will create clustered primary keys on a table. On the other hand, you will find Oracle DBAs shake their heads in amazement as to why one would do such a thing. As with all practices” that are so widespread in the SQL Server “best community the problem is that things typically a little more nuanced than the – whitepapers would have you believe. And depends” is the worst cop-out “it ever. Good Reasons To Cluster As you may have guessed, this post is about the advantages of heaps. But before we get there, let us first review the good reasons for using a clustered index on a table. Tables With A Single Index On An Ascending Key If your has a single index on a primary key, you probably want a cluster index. This is the default behaviour of SQL Server. But WHY is this so? Consider the case of INSERT statements in these two tables: 1 CREATE TABLE FooCluster 2 ( 3 ID INT NOT NULL PRIMARY KEY CLUSTERED 4 , Payload CHAR(150) NOT NULL 5 )
At Stack Overflow the other day, I once again found myself trying to debunk a lot of the “revealed wisdom” in the SQL Server community. You can find the post here: to read the discussion. However, this post is not about GUID or sequential keys, which I have written about , it is about cluster indexes and the love affair that SQL Server DBAs seem to have with them. It is interesting to note that both SQL Server and MySQL by default will create clustered primary keys on a table. On the other hand, you will find Oracle DBAs shake their heads in amazement as to why one would do such a thing. As with all “best practices” that are so widespread in the SQL Server community – the problem is that things typically a little more nuanced than the whitepapers would have you believe. And “it depends” is the worst cop-out ever. Good Reasons To Cluster As you may have guessed, this post is about the advantages of heaps. But before we get there, let us first review the good reasons for using a clustered index on a table. Tables With A Single Index On An Ascending Key If your has a single index on a primary key, you probably want a cluster index. This is the default behaviour of SQL Server. But WHY is this so? Consider the case of INSERT statements in these two tables: 1 2 3 4 5 CREATE TABLE FooCluster ( ID INT NOT NULL PRIMARY KEY CLUSTERED , Payload CHAR(150) NOT NULL )
Difference%20Between%20index%20seek%20and%20index%20scan.docx