I’ve just come across this feature of Sql Server which apparently has been around since Sql 2008 (code in this example tested in Sql Server 2014). There might be good reasons for wanting to use a GUID in a clustered primary key, but since the key is random there may be issues later on with INSERTs into the table.  A value which was unique and also sequential might be the answer. This function returns such a value.

It’s described here: MSDN: NEWSEQUENTIALID. As you’ll see there, it’s restricted to use as a default. The following code demonstrates:

--DROP TABLE T1
CREATE TABLE T1
(
C1 int NOT NULL
,C2 uniqueidentifier NOT NULL DEFAULT(NEWSEQUENTIALID())
);


SET NOCOUNT ON
DECLARE @Counter int = 0
WHILE @Counter < 100
BEGIN
INSERT INTO T1
(
C1
)
VALUES
(
@Counter + 1
)

SET @Counter+= 1

END;

Now the table is created and populated, we’ll run a quick test to validate that the ordering of the unique identifiers matches the rows in the table:
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY C1) As QC1
,ROW_NUMBER() OVER (ORDER BY C2) As QC2
FROM T1
) D1
WHERE QC1 <> QC2


As the documentation states, you’ll get an error if you try to use the function for anything other than a default:
SELECT NEWSEQUENTIALID()

I gathered that with a bit of work it was possible to read the value into a variable, so I wrote up something to do that.  It’s just draft code so please don’t use it without testing and consideration of possible issues.

SET NOCOUNT ON
DECLARE @Table TABLE(C1 uniqueidentifier NOT NULL DEFAULT(NEWSEQUENTIALID()), EnforceOneRow int NOT NULL PRIMARY KEY(EnforceOneRow) CHECK(EnforceOneRow = 1) )
DECLARE @SeqIdTable TABLE(C1 uniqueidentifier)
DECLARE @SeqUId uniqueidentifier
INSERT INTO @Table(EnforceOneRow ) VALUES(1)
UPDATE @Table SET C1 = DEFAULT
OUTPUT Inserted.C1 INTO @SeqIdTable
SELECT @SeqUId = C1 FROM @SeqIdTable
PRINT @SeqUId