Autoincrement uniqueidentifier

Question

Basically I want to use uniqueidentifier in similar way as identity. I don't want to insert values into it, It should just insert values automatically, different value for each row. I'm not able to set autoincrement on columns of type uniqueidentifier(the property 'autoincrement' is set to false and is not editable).

marc_s · Accepted Answer · 2010-05-14T21:14:55.670

32

Or even better: use the newsequentialid() as the default for your UNIQUEIDENITIFER column. That'll give you a somewhat sequential series of GUIDs.

CREATE TABLE dbo.YourTable   
   (SerialID UNIQUEIDENTIFIER 
        CONSTRAINT DF_SerialID DEFAULT newsequentialid(),
     .... (other columns)......
   )

Trouble is: newsequentialid is only available as a column default - you cannot call it as a function or anything. But that seems to fit your requirements.

UPDATE: there appears to be an acknowledged bug in SQL Server Management Studio that prevents specifying newsequentialid() as the default for a column in the interactive table designer.

See: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/cad8a4d7-714f-44a2-adb0-569655ac66e6

Workaround: create your table without specifying any default, and then type in this T-SQL statement in a normal query window and run it:

ALTER TABLE dbo.YourTable
    ADD CONSTRAINT DF_SerialID DEFAULT newsequentialid() FOR SerialID

That should do the trick!

edited May 14 '10 at 21:14

answered May 14 '10 at 20:57

marc_s

732,580
175
1,330
1,459

That is not trouble at all for me. Your answer seems to be what I was looking for. I'm going to try it :) – Rasto May 14 '10 at 20:59
I got "Error validating the default for Column XY". Could you please control your code(a typo?) ? – Rasto May 14 '10 at 21:02
@drasto: I ran that code on SQL Server 2008 R2 before I posted it - it works. What *version* of SQL Server do you have??? I removed extra brackets around the newsequentialid() - maybe that is a problem? Is works on 2008R2 for sure – marc_s May 14 '10 at 21:04
The one that is pre-installed in VS 2010 Ultimate edition. I'm not sure where I find the version of the server but I'll look for it. – Rasto May 14 '10 at 21:07
@drasto: seems to be a SQL Server Management Studio bug: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/cad8a4d7-714f-44a2-adb0-569655ac66e6 - type in the statement as SQL code and run it! – marc_s May 14 '10 at 21:10
I can try to ignore the error... It asks me if I want to keep the settings or cancel because of error. But I guess it will not work. – Rasto May 14 '10 at 21:10
Finally I decide to use identity but I guess your solution would work. – Rasto May 15 '10 at 02:08
If the unique-identity field is sequential will clustering and paging be an issue still if the field is a primary key? – Justin T. Watts Jun 26 '15 at 14:21
@JustinT.Watts: yes - it's still less optimal than using an `INT IDENTITY` (since GUID is 16 vs 4-byte - 4x larger) but it's less worrisome than the totally random `newid()` GUID's. I still prefer `INT IDENTITY` – marc_s Jun 26 '15 at 14:22
@marc_s I agree it is larger, I'm only wondering because we ran out of IDs with INT at work and was wondering why not just use GUID and not worry about it for 1000 years or so, lol, is there a study on how much more performant INT or BIGINT is compared to a sequential GUID? – Justin T. Watts Aug 05 '15 at 16:33
@marc_s is this newsequentialid() method predefined in sql server? what is different if we use int and set it's autoincrement to true? – Ali.Rashidi Dec 29 '16 at 11:55

BrunoLM · Answer 2 · 2010-05-14T22:50:34.643

7

I guess you mean in SQLServer and not C#...

Set the column as PRIMARY KEY and ROWGUID

RowGuid http://img341.imageshack.us/img341/8867/sqlserverrowguid.png

edited May 14 '10 at 22:50

answered May 14 '10 at 21:20

BrunoLM

97,872
84
296
452

3

just make sure **NOT** use make it the clustered index on the table!! – marc_s May 14 '10 at 21:24
Why not? I'm using it on a project and works fine, everything looks normal, checked the execution plan, table ordering, everything is just normal... – BrunoLM May 14 '10 at 22:54
2

GUID as clustered index are horribly bad: check out http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx - they lead to massive page and index fragmentation – marc_s May 15 '10 at 07:17
@marc_s is storing it as `nvarchar(36)` better then for `CI` ? – Zapnologica Aug 09 '16 at 16:24
1

@Zapnologica: **no!** It has the same problems: much too wide, and totally random - and in addition, it's now *variable length* - which makes it even worse! – marc_s Aug 09 '16 at 16:26
@marc_s, I just wanted to clear up if it was the concept of using a 16byte Guid that is bad, or just the fact that it was defined as `UniqueIdentifier' – Zapnologica Aug 09 '16 at 16:29
@Zapnologica: the two main problems with the GUID are **(1)** it's a rather *wide* key (16 byte vs. e.g. 4 byte for an `INT`), and **(2)** by default, GUID's are totally random, leading to massive index fragmentation – marc_s Aug 09 '16 at 17:16

score 4 · Answer 3 · answered Oct 10 '13 at 12:17

4

I think

CREATE TABLE dbo.YourTable
(
    SerialID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT newsequentialid()
)

is simplier

answered Oct 10 '13 at 12:17

Andrii

1,081
1
11
24

score 3 · Answer 4 · answered May 14 '10 at 20:56

3

Use NewID() as the default value. At least this is what you would do for SQL Server.

answered May 14 '10 at 20:56

Randy Minder

47,200
49
204
358

I've tried this but it seems to generate always the same identifier (this one: "00000000-0000-0000-0000-000000000000"). So when inserting the second row I get the error (tying to insert primary key that is already used) – Rasto May 14 '10 at 21:05
you should set newId() directly in table definition within your database! – MUG4N May 14 '10 at 21:08

Autoincrement uniqueidentifier

4 Answers4

Linked