Sequential NEWID()

Question

Hi, I am using sql server 2008. Is there a way to generate a unique sequential number? It should exhibit properties of NEWID() and an identity column, meaning that it is always unique but each subsequent value is greater than the previous one. It can't be datetime/datetime2, because it is not unique enough.

Thanks.

Answer 1

You can define a newsequentialid() as the default value for a UNIQUEIDENTIFIER column in SQL Server 2008 - unfortunately, that's all you can do.

CREATE TABLE dbo.MyTable
(
    ID UNIQUEIDENTIFIER DEFAULT (newsequentialid())
    ......
)

There's no way to generate sequential ID's (GUIDs) any other way :-(

Answer 2

CREATE TABLE demo_table (
    demo_id    uniqueidentifier NOT NULL,
    demo_value int
);

ALTER TABLE demo_table  ADD CONSTRAINT dc_demo
                        DEFAULT NEWSEQUENTIALID()
                        FOR demo_id;

You must have use a default constraint for sequential uniqueidentifiers.

Unfortunately, the limitation of NEWSEQUENTIALID() is that it does not guarantee the sequence after machine reboots.

The main objective of NEWSEQUENTIALID() is to have fast B-tree inserts, and the machine-reboot issue does not affect this. This is documented here:

• MSDN: NEWSEQUENTIALID() (Transact-SQL)
• NEWSEQUENTIALID() is Not Sequential

You can check this article about performance comparisons between NEWID(), NEWSEQUENTIALID() and IDENTITY: "The Code Project: Performance Comparison - Identity() x NewId() x NewSequentialId".

Answer 3

For strictly monotonically increasing after a restart, use numeric IDENTITY. NEWSEQUENTIALID does not support this.

You can't have your cake and eat it unless you invent your own combined column. However, this makes just things wider and more cumbersome to use.

Given that both IDENTITY and NEWSEQUENTIALID should be internal use only (eg not human readable), why this requirement?

Answer 4

The ROWVERSION data type is one possibility, although it's not suitable for use as a primary key; it is also automatically set.

Another option would be to create it yourself. Have a table with a column of the width you require, and have some code that atomically increments the value and returns it. With appropriate locking or transactions, you could use that to generate a value that would be suitable as a primary key.

You could also do something similar with a SQL CLR function. It might be possible to make it more efficient that way, too. For example, you could use the current time, but store the last value returned in a static. Then, if the next value is the same as the last one, increment by one.