sql server 2008 newsequentialid() problem

Question

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

Answer 1

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

Answer 2

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?