Primary Key With Non Clustered Index

28 Apr

As a default, SQL Server creates a Primary Key column with a Clustered Index. However, this doesn’t have to be the case.

Background

I was recently asked to create a table where the Primary Key is required to be a UniqueIdentifier (GUID). Although unusual, there are reasons that this design required this particular configuration.
As most DBAs will probably know, this is a good method of creating a constantly fragmented table, because a GUID will be a random value and thereby will place any inserted rows pretty much anywhere within the table. There is of course the NEWSEQUENTIALID data type but this will reset if the server is restarted, so can still have the same issue – just to a lesser degree.

Solution

However, just because the Primary Key needs to be this GUID, it doesn’t mean that I have to have a Clustered Index on it. I could use another column for that and thereby subject the table to less abuse.

Creating a test table as normally would, with a Primary Key that has a Clustered Index:

CREATE TABLE [dbo].[PK_Test1](
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[GUID_ID] [UNIQUEIDENTIFIER] NOT NULL
 CONSTRAINT [PK_PK_Test1] PRIMARY KEY CLUSTERED 
(
	GUID_ID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

And the result is as we usually see:

Default Primary Key results

Now create a table where I specify that the GUID is a NONCLUSTERED INDEX and place create the CLUSTERED INDEX against the ID column:

CREATE TABLE [dbo].[PK_Test2](
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[GUID_ID] [UNIQUEIDENTIFIER] NOT NULL
 CONSTRAINT [PK_PK_Test2] PRIMARY KEY NONCLUSTERED 
(
	GUID_ID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [IX_PK_Test2_ID] ON [dbo].[PK_Test2]
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Now we can see that the ID column has the Clustered Index, so will effectively specify the order the data will be inserted into the table. The Primary Key is still against the required UNIQUEIDENTIFIER column, but inserting data with this table definition reduces the chances of fragmentation.

Separate Clustered Index and Primary Key

Further Reading
GUIDS as Primary Keys
NEWSEQUENTIALID
GUID vs INT Debate

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: