Adding a uniqueidentifier column and adding the default to generate new guid

Question

I have the following SQL command:

ALTER TABLE dbo.UserProfiles
ADD ChatId UniqueIdentifier NOT NULL,
UNIQUE(ChatId),
CONSTRAINT "ChatId_default" SET DEFAULT newid()

I want to be able to make this column unique, and I want it to be able to generate a new guid every time a row is added to the table. This column is not an IDENTITY column because I already have one. This is something separate. How would I go about adding this column to a table with users already in it.

I'm not a SQL Server expert, but I believe that you should: (1) create it nullable (2) run an update like set = NewGuid() where is null (3) alter it to not null default NewGuid() — Andre Calil
– Andre Calil, Commented Aug 15, 2012 at 18:29
Why would you need an arbitrary unique column value if you already have a unique identity column? Are you migrating to a new key system in that table? — pseudocoder
– pseudocoder, Commented Aug 15, 2012 at 18:32
@AndreCalil is correct in the methodology, although I think the correct function name for T-SQL is NEWID() — PinnyM
– PinnyM, Commented Aug 15, 2012 at 18:32
@pseudocoder, one purpose that comes to mind is to use as an external reference in a system that pulls from various sources. Integers that are unique within the source scope are likely collide when moving to the aggregate system. — PinnyM
– PinnyM, Commented Aug 15, 2012 at 18:36
@pseudocoder replication also requires that you set up unique guid columns — Icarus
– Icarus, Commented Aug 19, 2012 at 12:55

Roger Medeiros · Accepted Answer · 2012-08-15 18:54:06Z

43

see this sample:

create table test (mycol UniqueIdentifier NOT NULL default newid(), name varchar(100))
insert into test (name) values ('Roger Medeiros')
select * from test

for add a not null field on a populated table you need this.

alter table test add mycol2 UniqueIdentifier NOT NULL default newid() with values

CREATE UNIQUE NONCLUSTERED INDEX IX_test ON dbo.test
(
mycol
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

edited Aug 15, 2012 at 18:54

answered Aug 15, 2012 at 18:36

Roger Medeiros

8138 silver badges10 bronze badges

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

4 Comments

PinnyM Over a year ago

As the OP explained, the table already exists. And it needs a unique constraint. And data has already been added

Roger Medeiros Over a year ago

I do not see the need for the restriction is that the field is of type unique identifier

PinnyM Over a year ago

That depends on if the data is ever passed in directly without using the default. Better safe than sorry...

Roger Medeiros Over a year ago

then just create the unique index for the column, see again the example.

Icarus · Accepted Answer · 2012-08-15 19:09:41Z

18

Don't use newid() as default, instead use newsequentialid(). newid() creates a lot of fragmentation and that's bad for indexes.

As far as adding the new column to a table with existing data, simply do this:

    ALTER TABLE your_table
    ADD your_column UNIQUEIDENTIFIER DEFAULT newsequentialid() NOT null

answered Aug 15, 2012 at 19:09

Icarus

64k14 gold badges102 silver badges116 bronze badges

3 Comments

Johann Over a year ago

If privacy is a concern, do not use newsequentialid(). It is possible to guess the value of the next generated GUID and, therefore, access data associated with that GUID

ashes999 Over a year ago

This is the tradeoff. Fragmentation or speed? Take your pick.

NeedHack Over a year ago

Fragmentation would only be an issue if the column is indexed.

Collectives™ on Stack Overflow

Adding a uniqueidentifier column and adding the default to generate new guid

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related