CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
cs0815
cs0815

Reputation: 17418

using bigint as non clustered key and guid/UNIQUEIDENTIFIER as primary key non clustered

Having read many blogs, I have decided to create tables that have a guid/UNIQUEIDENTIFIER as primary key, however using a non clustered index and a bigint as clustered key.

First of all what would be the correct DDL syntax in this scenario. Also, I would think that using the bigints as foreign keys would be the correct choice. Is this correct?

Here is a starter for ten:

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL
  DROP TABLE dbo.Table1; 

CREATE TABLE dbo.Table1
(
    [Table1Id] [BIGINT] IDENTITY(1,1) NOT NULL,
    [Table1Guid] [UNIQUEIDENTIFIER] NOT NULL,
    [PayLoad] NVARCHAR(200) NULL
PRIMARY KEY CLUSTERED 
(
    [Table1Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
) 

CREATE NONCLUSTERED INDEX IX_Table1_Table1Guid
    ON dbo.Table1 (Table1Guid); 
GO

Upvotes: 0

Views: 609

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Actually when you create table by default primary key is created as clustered index. But you can create nonclustered primary key and add clustered index on another column. The syntax would be:

CREATE TABLE [dbo].[Table1](
    [Table1Id] [BIGINT] IDENTITY(1,1) NOT NULL,
    [Guid] [UNIQUEIDENTIFIER] NOT NULL,
    [PayLoad] NVARCHAR(200) NULL
) 
GO

ALTER TABLE [dbo].[Table1] ADD CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED ([Guid])
GO

CREATE UNIQUE CLUSTERED INDEX IX_Table1_Table1Id ON dbo.Table1 (Table1Id)
GO

Now about the primary key on guid column. It is usually a bad idea to have guid primary key because of space required.

Taken from Exam 70-461: Querying Microsoft SQL Server 2012:

The storage requirements for your surrogate key can have a cascading effect if your clustered index is defined on the same key columns (the default for a primary key constraint). The clustered index key columns are used by all nonclustered indexes internally as the means to locate rows in the table. So if you define a clustered index on a column x, and nonclustered indexes—one on column a, one on b, and one on c—your nonclustered indexes are internally created on column (a, x), (b, x), and (c, x), respectively.

Unless you really need such a PK(for example in distributed systems, when you need uniqueneess across several systems) I would not recommend to use guids as PK, at least in operational tables.

Upvotes: 1

Related Questions