Notes on SQL

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:

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.

Further Reading
GUIDS as Primary Keys
NEWSEQUENTIALID
GUID vs INT Debate