Is the primary key always clustered in SQL Server?

As I was playing in SSMS (SQL Server Management Studio), I created a table using the plain-old T-SQL, and then I decided to create a primary key, the “wizard” way, so to speak.

Let’s have a repro here:

CREATE TABLE [dbo].[Employees]
    (
      [empID] [int] NOT NULL ,
      [firstname] [varchar](30) NOT NULL ,
      [lastname] [varchar](30) NOT NULL ,
      [salary] [money] NOT NULL,
    );

Okay, now, in order to create a primary key on the empID column, I can either:

Use the following T-SQL:

ALTER TABLE dbo.Employees ADD CONSTRAINT
	PK_Employees PRIMARY KEY
	(
	empID
	)

Or use the wizard:

• Expand your database node (testdb, in my case), navigate to your table, right-click it and select Design from the menu. Do not worry about the extra menus and buttons that you see, they are installed when you install SQL Toolbelt, a wonderful gift that I have from our friends at Redgate.

• Select the column header that you wish to be your primary key (that is, in the Designer tab) and then click the Set Primary Key button (should look like this )
• Click Generate Change Script (right to the left of Set Primary Key). The Save Change Script dialog box is displayed. Right-click and select Select All.
  

• Copy the text and close the window by selecting No. Paste the text in a new query window:

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Employees ADD CONSTRAINT
PK_Employees PRIMARY KEY CLUSTERED
(
empID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Employees SET (LOCK_ESCALATION = TABLE
)
GO
COMMIT

I can see some problems already with this approach:

• The first part is that there’s a BEGIN TRANSACTION for all the database properties. Why is this there? I have no idea, but presumably it looks cool, even if useless.
• The second part is that there’s the ADD CONSTRAINT that goes with PRIMARY KEY CLUSTERED. Clustered? When did I say that? J
• Lastly, the LOCK_ESCALATION = TABLE might not be the best idea, at least not always

OK, now that we spotted some issues, I presume it’s safer to go with the script instead of the wizard:

ALTER TABLE dbo.Employees ADD CONSTRAINT
	PK_Employees PRIMARY KEY
	(
	empID
	)

Now I have successfully created my primary key, so let’s go check it:

• Expand the Employees table, and then expand the Indexes node.

Shoot, the primary key is still clustered!

The answer is this: if you don’t have a clustered index on the table and you don’t define your primary key as being non-clustered, it will assume on your behalf that there should be a clustered index on your primary key.

I hope this helps.

Best regards,
io