The Question

The IDs generated by NEWSEQUENTIALID() can be guessable.  What would happen if the next sequential ID was guessed, inserted into a column and then NEWSEQUENTIALID() used for the next row?  Would there be a clash of values?

The Answer

NEWSEQUENTIALID() will skip to another (higher) range to avoid a GUID clash.

It seems to do this whether or not there is a unique index on the uniqueidentifier column.

The Test

CREATE DATABASE TestSeqIDs
GO
USE TestSeqIDs

CREATE TABLE TestTable
( SequentialId uniqueidentifier PRIMARY KEY DEFAULT NewSequentialID()
 ,Comment varchar(100)
)
GO
DECLARE @i int = 0
WHILE (@i < 4)
BEGIN	
	INSERT INTO TestTable (Comment) VALUES ('NEWSEQUENTIALID() in While loop (1)')
	SET @i += 1
END

INSERT INTO TestTable  (Comment) VALUES ('NEWSEQUENTIALID() in single statement (1)')
INSERT INTO TestTable  (Comment) VALUES ('NEWSEQUENTIALID() in single statement (1)')

SELECT * FROM TestTable

/*
SequentialId	Comment
6D2B9ED6-F14A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in While loop (1)
6E2B9ED6-F14A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in While loop (1)
6F2B9ED6-F14A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in While loop (1)
702B9ED6-F14A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in While loop (1)
712B9ED6-F14A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in single statement (1)
722B9ED6-F14A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in single statement (1)
*/

-- Manually insert the next ID in the sequence - i.e. intended to clash with next sequential ID

INSERT INTO TestTable  (SequentialId, Comment) 
	VALUES ('732B9ED6-F14A-E411-ABEF-C81F662CE547', 'Manually entered UID') -- Edit to suit!


GO
-- Add some more sequential generated IDs

DECLARE @i int = 0
WHILE (@i < 4)
BEGIN	
	INSERT INTO TestTable (Comment) VALUES ('NEWSEQUENTIALID() in While loop (2)')
	SET @i += 1
END
INSERT INTO TestTable  (Comment) VALUES ('NEWSEQUENTIALID() in single statement (2)')
INSERT INTO TestTable  (Comment) VALUES ('NEWSEQUENTIALID() in single statement (2)')
GO
INSERT INTO TestTable  (Comment) VALUES ('NEWSEQUENTIALID() in single statement new batch (post-GO)')
INSERT INTO TestTable  (Comment) VALUES ('NEWSEQUENTIALID() in single statement new batch (post-GO)')

-- what happens?

SELECT * FROM TestTable
/*
SequentialId	Comment
6D2B9ED6-F14A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in While loop (1)
6E2B9ED6-F14A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in While loop (1)
6F2B9ED6-F14A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in While loop (1)
702B9ED6-F14A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in While loop (1)
712B9ED6-F14A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in single statement (1)
722B9ED6-F14A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in single statement (1)
732B9ED6-F14A-E411-ABEF-C81F662CE547	Manually entered UID
854BBD18-F24A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in While loop (2)
864BBD18-F24A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in While loop (2)
874BBD18-F24A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in While loop (2)
884BBD18-F24A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in While loop (2)
894BBD18-F24A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in single statement (2)
8A4BBD18-F24A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in single statement (2)
8B4BBD18-F24A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in single statement new batch (post-GO)
8C4BBD18-F24A-E411-ABEF-C81F662CE547	NEWSEQUENTIALID() in single statement new batch (post-GO)
*/


-- There was no clash. NewSequentialId moved on to another higher range.



-- Drop database
Use Master 
ALTER DATABASE TestSeqIDs SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE TestSeqIDs

 Reference

From Books Online http://msdn.microsoft.com/en-gb/library/ms189786.aspx.  The definitions don’t exactly define this behaviour.

NEWSEQUENTIALID() … Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique.

…

Each GUID generated by using NEWSEQUENTIALID() is unique on that computer. GUIDs generated by using NEWSEQUENTIALID() are unique across multiple computers only if the source computer has a network card.