Sql server 2008 newsequentialid () problem

Question

I'm having newsequentialid () problems in sql server management studios. Create a table with a unique "UniqueID" column and set the default value to newsequentialid ().

Step 1. Saving the design:

Table "Table_1" - Error checking the default value for the "UniqueID" column.

Save it anyway.

Step 2. Browse sql:

CREATE TABLE [dbo].[Table_1]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL, [UniqueID] [uniqueidentifier] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_UniqueID] DEFAULT (newsequentialid()) FOR [UniqueID] GO 

It looks reasonable.

Step 3. Add a few lines:

 1 test 72b48f77-0e26-de11-acd4-001bfc39ff92 2 test2 92f0fc8f-0e26-de11-acd4-001bfc39ff92 3 test3 122aa19b-0e26-de11-acd4-001bfc39ff92 

They do not look very consistent. ??

Editing: I got it to work a little, if the inserts are all done at once, then the unique identifier is consistent. On later inserts, the sql server seems to forget the last consecutive identifier and start a new sequence.

Running this in ssms leads to nasty commands:

 insert into Table_1 (Name) values('test13a'); insert into Table_1 (Name) values('test14a'); insert into Table_1 (Name) values('test15a'); insert into Table_1 (Name) values('test16a'); insert into Table_1 (Name) values('test17a'); 

Answer 1

newsequentialid primarily solves the problem of page fragmentation when your table is clustered with a unique identifier. Your table is clustered by an entire column. I set up two test tables where the newsequentialid column is the primary key and the other where there is none (like yours), and in the primary key, the GUIDs have always been sequential. They were not in another.

I don't know the internal reasons / technical reasons why it behaves this way, but it seems clear that newsequentialid () is really really consistent when your table is clustered by it. Otherwise, it seems to work similarly to newid () / RowGuid.

Also, I'm curious why you want to use newsequentialid () when you don't need it. It has many disadvantages that newid () does not have, and none of the advantages is the biggest that newid () is not practically predictable, while newsequentialid () is. If fragmentation doesn't bother you, what's the point?

Answer 2

These values ​​are actually "sequential" by the definition of NEWSEQUENTIALID ():

Creates a GUID that is larger than any GUID previously generated by this function on the specified computer since Windows started.

It does not say that there can be no spaces in a GUID, since any new GUID must be larger than the previous one.

Try the following:

 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 

As you can see, the entries are ordered 1, 2, 3, as expected.

Answer 3

THEY ARE CONSECUTIVE!

 1 test 72b48f77-0e26-de11-acd4-001bfc39ff92 2 test2 92f0fc8f-0e26-de11-acd4-001bfc39ff92 3 test3 122aa19b-0e26-de11-acd4-001bfc39ff92 

<p> 77 <8f <9b !!! You should see the highest bytes, not the lowest (from right to left)

Answer 4

I am not familiar with newsequentialid (), for uniqueidentifier types I call newid ().

Answer 5

There can definitely be gaps in the NewSequentialId() sequences - I found the following reasons for the gaps:

• As soon as another call is made by another table requiring NewSequentialId ()
• Bad insertions
• Rollbacks

(2 and 3 are similar to identity () in this regard)

For example, for two tables using NewSequentialId()

 create table XXX(someGuid uniqueidentifier DEFAULT NEWSEQUENTIALID(), x INT) create table YYY(someGuid uniqueidentifier DEFAULT NEWSEQUENTIALID(), y DateTime) 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 07:13:35.503 909438E1-CCA3-E111-9E8E-005056C00008 2012-05-22 07:13:41.210 919438E1-CCA3-E111-9E8E-005056C00008 2012-05-22 07:13:41.220 

In addition, NewSequentialId () s are not returned to the sequence in the event of a failed insert, for example.

 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 ie 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 

Answer 6

NEWSEQUENTIALGUID (since each guid created in a way that guarantees their consistency) includes a part of the Guid calculated using a timestamp. Therefore, if you start inserts at different times, you will see some spaces.

But the important part is that Guid are “ordered” in such a way as not to cause page breaks (if Guid is used in the index), and this is what happens when using the new sequential pointer.