CODEWITHSUNDEEP
sql-server
P a u l
P a u l

Reputation: 7911

sql server 2008 newsequentialid() problem

I'm having newsequentialid() learning problems in sql server management studio. Create a table with a uniqueidentifier column 'UniqueID', and set the default to newsequentialid().

Step 1. saving the design:

'Table_1' table - Error validating the default for column 'UniqueID'.

Save it anyway.

Step 2. view the 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

Looks reasonable.

Step 3. add some rows:

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

They don't look very sequential. ??

Edit: I have gotten it to work somewhat if the inserts are all done at once, then the unique id is sequential. On later inserts, sql server seems to forget the last sequential id, and starts a new sequence.

Running this in ssms results in squential guids:

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');

Upvotes: 6

Views: 8524

Answers (6)

Lucio Menci
Lucio Menci

Reputation: 143

THEY ARE SEQUENTIAL!

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

77 < 8f < 9b !!! You have to see the highest value byets, not the lowest (from right to left)

Upvotes: 3

massimogentilini
massimogentilini

Reputation: 4172

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.

Upvotes: 0

StuartLC
StuartLC

Reputation: 107237

There definitely can be gaps in NewSequentialId() sequences - I've found the following causes gaps:

  1. As soon as another call is made by another table needing a NewSequentialId()
  2. Failed inserts
  3. Rollbacks

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

For example, given 2 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

Also, NewSequentialId()s aren't returned to the sequence in the case of a failed insert, e.g. 

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

Upvotes: 0

Valentino Vranken
Valentino Vranken

Reputation: 5815

Those values are actually "sequential" as per the definition of NEWSEQUENTIALID():

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

It doesn't say there can't be any gaps in the GUIDs, it's just that any new GUID should be greater than the previous one.

Try this:

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 records are ordered 1, 2, 3 which is as expected.

Upvotes: 5

Rex M
Rex M

Reputation: 144112

newsequentialid is primarily to solve the issue of page fragmentation when your table is clustered by a uniqueidentifier. Your table is clustered by an integer column. I set up two test tables, one where the newsequentialid column is the primary key and one where it is not (like yours), and in the primary key the GUIDs were always sequential. In the other, they were not.

I do not know the internals/technical reasons why it behaves that way, but it seems clear that newsequentialid() is only truly sequential when your table is clustered by it. Otherwise, it seems to behave similarly to newid() / RowGuid.

Also, I'm curious as to why you would want to use newsequentialid() when you don't have to. 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?

Upvotes: 5

SirDemon
SirDemon

Reputation: 1758

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

Upvotes: 0

Related Questions