Error validating the default for column id newsequentialid

posted by | Leave a comment

create table #test(id int, txt varchar(50), gid uniqueidentifier) insert into #test select 1 ,'test','72b48f77-0e26-de11-acd4-001bfc39ff92' union select 2, 'test2', '92f0fc8f-0e26-de11-acd4-001bfc39ff92' union select 3, 'test3', '122aa19b-0e26-de11-acd4-001bfc39ff92' select * from #test order by gid asc create table XXX(some Guid uniqueidentifier DEFAULT NEWSEQUENTIALID(), x INT) create table YYY(some Guid uniqueidentifier DEFAULT NEWSEQUENTIALID(), y Date Time) GO insert into XXX(x) values(1) insert into XXX(x) values(2) insert into XXX(x) values(3) GO insert into YYY(y) values(current_timestamp) insert into YYY(y) values(current_timestamp) insert into YYY(y) values(current_timestamp) GO insert into XXX(x) values(4) insert into XXX(x) values(5) insert into XXX(x) values(6) GO SELECT * FROM XXX 6A6E85CB-CCA3-E111-9E8E-005056C00008 1 6B6E85CB-CCA3-E111-9E8E-005056C00008 2 6C6E85CB-CCA3-E111-9E8E-005056C00008 3 **CCEA7AF2-CCA3-E111-9E8E-005056C00008 4** Gap here because we 'switched' to y CDEA7AF2-CCA3-E111-9E8E-005056C00008 5 CEEA7AF2-CCA3-E111-9E8E-005056C00008 6 SELECT * FROM YYY 8F9438E1-CCA3-E111-9E8E-005056C00008 2012-05-22 .503 909438E1-CCA3-E111-9E8E-005056C00008 2012-05-22 .210 919438E1-CCA3-E111-9E8E-005056C00008 2012-05-22 .220 insert into XXX(x) values(1) insert into XXX(x) values(2) BEGIN TRAN insert into XXX(x) values(3) insert into XXX(x) values(4) ROLLBACK TRAN insert into XXX(x) values(5) insert into XXX(x) values(6) GO 686EFE5B-CDA3-E111-9E8E-005056C00008 696EFE5B-CDA3-E111-9E8E-005056C00008 6C6EFE5B-CDA3-E111-9E8E-005056C00008 6D6EFE5B-CDA3-E111-9E8E-005056C00008 i.e.

a Gap of 2 Guids rolled back and insert into XXX(x) values(1) insert into XXX(x) values(2) insert into XXX(x) values(3) GO insert into XXX(x) values(99999999999999) -- overflow GO insert into XXX(x) values(4) insert into XXX(x) values(5) insert into XXX(x) values(6) go select * from xxx AC613611-CFA3-E111-9E8E-005056C00008 1 AD613611-CFA3-E111-9E8E-005056C00008 2 AE613611-CFA3-E111-9E8E-005056C00008 3 **B0613611-CFA3-E111-9E8E-005056C00008 4** Gap of 1 - overflow failure B1613611-CFA3-E111-9E8E-005056C00008 5 B2613611-CFA3-E111-9E8E-005056C00008 6 NEWSEQUENTIALGUID (as every guid generated in a way that warrant their sequence) includes a part of the Guid calculated via a time stamp.

So if you run the inserts at different time you'll see some gaps.

But the important part is that the Guid are "ordered" in a way that do not cause page splits (if the Guid is used in a index) and this is what happens when using the new sequential guid.

When NEWSEQUENTIALID() is used in DEFAULT expressions, it cannot be combined with other scalar operators.

For example, you cannot execute the following: CREATE TABLE my Table (Column A uniqueidentifier DEFAULT dbo.myfunction(NEWSEQUENTIALID())) ...

Microsoft is conducting an online survey to understand your opinion of the Msdn Web site.

If you choose to participate, the online survey will be presented to you when you leave the Msdn Web site.

Having used the upsizing wizard to move my Access2003 Jet-synchronized DB to SQL 2005 I find the rowguid-field with (newsequentialid()) as Default-Value.

It has many downsides which newid() does not, and none of the benefits - the biggest being that newid() is not practically predictable, whereas newsequentialid() is.

If you are not worried about fragmentation, what's the point?

To insert a globally unique value, create a DEFAULT definition on the column that uses the NEWID or NEWSEQUENTIALID function to generate a globally unique value.

I have a column in my file with the 'uniqueidentifier' datatype.

Leave a Reply

operaupdating ru