Primary Keys and the Default Clustered Index

When it comes to determining what your clustered index should be for a given table, you often think of the primary key.  Often times that is a good choice.  In fact, that is the default is to do that.  That is not always your best choice, though, depending on how your data is being accessed.  Here I am going to show some different ways in which you can create your PK and your clustered index as well as some default behavior that you may not be aware of.

When you create a table via the GUI  (SQL Server Management Studio) and select a column to be the primary key, like this…

It generates a create and an alter table statement as shown below.  Note the key work CLUSTERED.  This is because there is no clustered index yet on the table

CREATE TABLE dbo.Table_1

                (

                pk int NOT NULL,

                notpk int NULL

                ) ON [PRIMARY]

GO

ALTER TABLE dbo.Table_1 ADD CONSTRAINT

                PK_Table_1 PRIMARY KEY CLUSTERED

                (

                pk

                ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

You can also simply create the table and specify PRIMARY KEY CLUSTERED with the CREATE TABLE itself, but the constraint and clustered index created by default will have a generated name that may not match naming conventions.  You can also leave out the word CLUSTERED altogether and it will create a clustered index on the primary key column by default.

CREATE TABLE dbo.Table_1

                (

                pk int NOT NULL PRIMARY KEY CLUSTERED,

                notpk int NULL

                )  ON [PRIMARY]

GO

You can specify NONCLUSTERED instead of clustered, and then a nonclustered index will be created instead

CREATE TABLE dbo.Table_1

                (

                pk int NOT NULL PRIMARY KEY NONCLUSTERED,

                notpk int NULL

                )  ON [PRIMARY]

GO

If you also create a table without a primary key and specify a clustered index.  You can later alter the table and add the PRIMARY KEY constraint.  This will also create a nonclustered index by default.  In this case, if you tried to specify the CLUSTERED keyword after PRIMARY KEY, you would get an error as a clustered index already exists.

CREATE TABLE dbo.Table_1

       (

       pk int NOT NULL,

       notpk int NULL

       )  ON [PRIMARY]

GO

CREATE CLUSTERED INDEX IX_Table_1 ON dbo.Table_1

       (

       notpk

       ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE dbo.Table_1

ADD CONSTRAINT PK_Table_1 PRIMARY KEY (pk)

You can then drop the nonclustered index and then have a heap table with a primary key (that has a nonclustered index on the primary key column).

I ALWAYS recommend having a clustered index on every table, but it is not always best to have it clustered on the primary key field. Hopefully this will show you some of the default behavior that SQL has in terms of PKs and the clustered index.