SQL Server 2005 introduced a new type of function to create a uniqueidentifier; the NEWSEQUENTIALID(). This new function has been created for performance reasons, each new value is greater than the previous value. In theory this means that the value will be inserted at the end of a page and not in the middle which can cause splits.
Let's run this code to see the difference
CREATE TABLE #TableSeqID (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID(),
ColumnB uniqueidentifier DEFAULT NEWID())
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
INSERT #TableSeqID DEFAULT VALUES
GO
SELECT * FROM #TableSeqID
Output
-----------
BBF765FE-57BD-DC11-875F-000D5684F8D8 CE51B9E4-1640-47E2-87C6-6ADD46C63A87
BCF765FE-57BD-DC11-875F-000D5684F8D8 CA220BAB-462E-440E-829A-E0037CAF0D1F
BDF765FE-57BD-DC11-875F-000D5684F8D8 01748772-8155-4F83-A58F-CC3253DDD3F3
BEF765FE-57BD-DC11-875F-000D5684F8D8 9C4B2C87-AE33-4432-8310-3BE731179382
BFF765FE-57BD-DC11-875F-000D5684F8D8 1F84B827-F42A-4C47-8A1B-4B672B4402F1
As you can see ColumnB is random (Or at least pseudo-random) while ColumnA is not
Let's say you have a table with a billion rows, this table used NEWID() up till now. What will happen when you change the table to use NEWSEQUENTIALID(), could you get a duplicate?
I tried my best and filled up my 400GB External Seagate drive without success
Have you run into a collision, is it even possible?
1 comment:
I originally thought about writing an interative loop but, b/c you can't use NEWSEQUENTIALID() outside of a table structure, you can't really do anything like that.
Itzhik is generally very thorough in his breakdowns (if not overly thorough lol) - have you read this article? http://www.sqlmag.com/Article/ArticleID/50164/Dont_Overlook_the_New_SQL_Server_2005_NEWSEQUENTIALID_Function.html
Perhaps it discusses it - I don't have a subscription to that mag so I can't say.
Post a Comment