Create table with UNIQUEIDENTIFIER as PK

Question

I have been trying (unsuccessfully) to create a table with a UNIQUEIDENTIFIER as the primary key. The script below will create the table just fine, however there is no PK specified. This is my first time using a UNIQUEIDENTIFIER, however. Any ideas?

T-SQL

CREATE TABLE [dbo].[AgentRelationshipCodes] ( Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(), RelationshipId char(3) NULL, EffectiveDate datetime NULL, LastChangeDate datetime NOT NULL, LastChangeId char(6) NOT NULL, AgtTableId int FOREIGN KEY REFERENCES AgentTransmission(ID) );

Management Studio

enter image description here

Just add: CONSTRAINT PK_AgentRelationshipCodes_ID PRIMARY KEY CLUSTERED (ID) or CONSTRAINT PK_AgentRelationshipCodes_ID PRIMARY KEY NONCLUSTERED (ID) — Lamak
– Lamak, Commented May 30, 2014 at 17:11
Or a bit of shorter: Add PRIMARY KEY [CLUSTERED] after NEWID(). — Hamlet Hakobyan
– Hamlet Hakobyan, Commented May 30, 2014 at 17:18
I would strongly advise against this set up. Have the unique identifier as your primary key by all means, but put in an identity column and make this your clustering key. Inserts get very expensive and you end up with huge fragmentation when you use unique identifiers as the clustering key (which it will be by default when you make it the primary key unless you explicitly do otherwise). If you must make it the clustering key, then use NEWSEQUENTIALID() as the default. — GarethD
– GarethD, Commented May 30, 2014 at 17:18
And while I think using a GUID is a horrible practice, it can be needed in some cases. However, don't use newID() use NEWSEQUENTIALID(). — HLGEM
– HLGEM, Commented May 30, 2014 at 17:19
@Lamak He doesn't say he does, but he doesn't say that he doesn't want it be clustered either, and by default the primary key will be the clustering key. There is also no other good candidate for a clustering key in the table. — GarethD
– GarethD, Commented May 30, 2014 at 17:22

Chris Schaller · Accepted Answer · 2019-10-09 03:25:12Z

7

create table [dbo].[AgentRelationshipCodes] ( Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(), . . . )

Repeat for the other tables.

Answered here by Christian Hayter

edited Oct 9, 2019 at 3:25

Chris Schaller

17.1k3 gold badges57 silver badges101 bronze badges

answered May 30, 2014 at 17:21

Mark C.

6,5004 gold badges39 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create table with UNIQUEIDENTIFIER as PK

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related