-3

Is that I have been creating a table in SQL that implements GUID, for it I review this question:

What are the best practices for using a GUID as a primary key, specifically regarding performance?

Here an answer tells me to use a GUID key and also another key, but with Identity, so that:

CREATE TABLE dbo.MyTable
(PKGUID UNIQUEIDENTIFIER NOT NULL,
 MyINT INT IDENTITY (1,1) NOT NULL,
 .... add more columns as needed ......)

ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY NONCLUSTERED (PKGUID)

CREATE UNIQUE CLUSTERED INDEX CIX_MyTable ON dbo.MyTable (MyINT)

My question is: why use an int key when it says that we are already using the GUID?

Wilmilcard
  • 281
  • 4
  • 13

1 Answers1

2

The confusion stems from the multiple meanings of the word "key" in the answer; to wit, the answer states:

if you want to have your PKGUID column as your primary key (but not your clustering key), and another column MYINT (INT IDENTITY) as your clustering key...

I would change it to say (changes bolded):

if you want to have your PKGUID column as your primary key (but not your clustered index), and another column MYINT (INT IDENTITY) as your clustered index...

The point is that by default, a PRIMARY KEY is buit on a clustered index (unless you specify otherwise); clustered indexes are then included in every other index, which, in the case of a GUID as a clustered PK, can be a significant performance bottleneck. The code you posted is a compromise; it satisfies the "need" to have a GUID for a Primary Key, while clustering on a smaller column value (which can lead to a performance boost).

It's not ideal, but it can be a very useful method. If you'd like to read up more on the differences between keys and indexes, here are some useful links:

What is the difference between a primary key and a index key https://itknowledgeexchange.techtarget.com/sql-server/difference-between-an-index-and-a-primary-key/ When should I use primary key or index?

Stuart Ainsworth
  • 12,792
  • 41
  • 46