T-SQL: Nonclustered Primary Keys

In the majority of SQL database applications I have worked with, surrogate keys are prevalent. Essentially, IDENTITY fields are used to define unique rows rather than working out which fields define the PRIMARY KEY for a table. I have no problem with this in principal and use this technique often myself, for example in Logging/Audit tables where I follow design principals that say every table should have a PK.

By default, when you define a primary key it is created as a CLUSTERED index. However with surrogate keys, the field is rarely used in queries and so essentially you have "wasted" your clustered index - in my view, your most prized index possession due the fact there can be only one per table. Therefore, I strongly recommend to create any primary keys as NONCLUSTERED which will allow you to make judicial use of your clustered index on columns which may benefit more. Of course, there are caveats to this, one example being if you are intending on creating XML indexes which requires that the table has a clustered primary key.

Futher to this, when creating tables a good habit I have got into is to create your table without defining constraints in the DDL and then adding the constraints as separate DDL statements. This reminds you to be a lot more explicit in your naming conventions and constraint/index definitions rather than relying on SQL Servers defaults.

An example below:

CREATE TABLE [dbo].[tblAudit](
[Audit_ID] [int] IDENTITY(1,1) NOT NULL,
[Audit_Type_ID] [int] NOT NULL,
[Date_Created] [datetime] NOT NULL,
[Created_By] [nvarchar](255) NULL,
[Audit_Description] [nvarchar](255) NULL
)
GO
ALTER TABLE [dbo].[tblAudit]
ADD CONSTRAINT [PK_tblAudit_AuditID] PRIMARY KEY ([Audit_ID])
GO
ALTER TABLE [dbo].[tblAudit] WITH CHECK ADD CONSTRAINT [FK_tblAudit_AuditTypeID] FOREIGN KEY([Audit_Type_ID])
REFERENCES [dbo].[tblAuditType] ([Audit_Type_ID])
GO
ALTER TABLE [dbo].[tblAudit] ADD CONSTRAINT [DF_tblAudit_DateCreated] DEFAULT (GETDATE()) FOR [Date_Created]
GO