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, Aug 15 '12 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, Aug 15 '12 at 18:32
@AndreCalil is correct in the methodology, although I think the correct function name for T-SQL is `NEWID()` — PinnyM, Aug 15 '12 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, Aug 15 '12 at 18:36
@pseudocoder replication also requires that you set up unique guid columns — Icarus, Aug 19 '12 at 12:55

Roger Medeiros · Accepted Answer · 2012-08-15T18:54:06.087

40

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 '12 at 18:54

answered Aug 15 '12 at 18:36

Roger Medeiros

783
7
9

As the OP explained, the table already exists. And it needs a unique constraint. And data has already been added – PinnyM Aug 15 '12 at 18:37
1

I do not see the need for the restriction is that the field is of type unique identifier – Roger Medeiros Aug 15 '12 at 18:44
That depends on if the data is ever passed in directly without using the `default`. Better safe than sorry... – PinnyM Aug 15 '12 at 18:46
1

then just create the unique index for the column, see again the example. – Roger Medeiros Aug 15 '12 at 18:55

score 18 · Answer 2 · answered Aug 15 '12 at 19:09

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 '12 at 19:09

Icarus

63,293
14
100
115

22

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 – Johann Oct 24 '13 at 07:49
This is the tradeoff. Fragmentation or speed? Take your pick. – ashes999 Dec 05 '13 at 17:40
3

Fragmentation would only be an issue if the column is indexed. – NeedHack May 29 '14 at 16:35

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

2 Answers2

Linked