codegumbo

Laissez Les Bon Code Roulez!

• Home
• About Me
• Code, etc.

Fun with NEWSEQUENTIALID()

We’re thinking about converting the constrain on an existing column in a table with billions of rows of data.  The column is a uniqueidentifier serving as a nonclustered primary key; we’re planning on replacing the NEWID() constraint to a NEWSEQUENTIALID().  What is challenging me is my concern over primary key collisions; will the new default values eventually run into an existing primary key value?

I used the following script to test, and I was a little surprised at the results:

CREATE TABLE TestNewSeqID(ID uniqueidentifier CONSTRAINT DF1 DEFAULT newID(),
                            Marker integer Identity(1,1))
GO

DECLARE @x int
SET @x= 1
WHILE @x <=10
BEGIN
    INSERT INTO TestNewSeqID DEFAULT VALUES
    SET @x = @x +1
END

SELECT *
FROM TestNewSeqID
ORDER BY ID DESC

ALTER TABLE TestNewSeqID
DROP CONSTRAINT DF1;

ALTER TABLE TestNewSeqID
ADD CONSTRAINT DF1 DEFAULT newsequentialid() for ID;

SET @x= 1
WHILE @x <=10
BEGIN
    INSERT INTO TestNewSeqID DEFAULT VALUES
    SET @x = @x +1
END

SELECT *
FROM TestNewSeqID
ORDER BY ID DESC

My results looked something like the following:

ID    Marker
F664E06A-51BF-4FC1-960E-C9E5E854ADAD    9
0746DBAC-8B14-463C-8C4E-9CC789794963    7
24217231-1865-4E2A-B8EF-944DEC9AC6A2    1
323C15B4-59DA-4B73-94E5-54A1A0860F3D    8
B0C1E7EE-3701-4916-A3E2-465FE8462965    2
BFE8C2AB-4788-4596-867E-221F75BAB338    10
C0BC2263-F5E6-41F1-83F6-14BD9D50A6DD    3
9FA2A683-FF02-458E-8703-10D1DCDEFA97    4
75445937-6B46-4B88-9525-0DD809A7BE4B    6
E93284BE-E93B-4009-B206-04CB2B107293    5
4390AD7A-CD7D-DE11-98B8-00196665566A    20
4290AD7A-CD7D-DE11-98B8-00196665566A    19
4190AD7A-CD7D-DE11-98B8-00196665566A    18
4090AD7A-CD7D-DE11-98B8-00196665566A    17
3F90AD7A-CD7D-DE11-98B8-00196665566A    16
3E90AD7A-CD7D-DE11-98B8-00196665566A    15
3D90AD7A-CD7D-DE11-98B8-00196665566A    14
3C90AD7A-CD7D-DE11-98B8-00196665566A    13
3B90AD7A-CD7D-DE11-98B8-00196665566A    12
3A90AD7A-CD7D-DE11-98B8-00196665566A    11

If I looped through several more times, the seed changed and the values got progressively higher, but they were still sorting lower than the original data set.  I’d appreciate it if others would run this script and see if they got the same results.

July 31, 2009 · stuart · One Comment
Posted in: SQL, SQL Server, SQLServerPedia Syndication

• « Previous post
• Next post »

• 

• Archives

  • February 2012 (1)
  • January 2012 (1)
  • November 2011 (1)
  • October 2011 (3)
  • September 2011 (3)
  • August 2011 (1)
  • July 2011 (2)
  • June 2011 (2)
  • May 2011 (5)
  • April 2011 (2)
  • March 2011 (4)
  • February 2011 (6)
  • January 2011 (6)
  • December 2010 (2)
  • November 2010 (4)
  • October 2010 (1)
  • September 2010 (4)
  • August 2010 (3)
  • July 2010 (5)
  • June 2010 (3)
  • May 2010 (4)
  • March 2010 (1)
  • February 2010 (2)
  • January 2010 (2)
  • December 2009 (1)
  • November 2009 (3)
  • October 2009 (12)
  • September 2009 (8)
  • August 2009 (3)
  • July 2009 (7)
  • June 2009 (3)
  • May 2009 (10)
  • April 2009 (9)
  • March 2009 (8)
  • February 2009 (8)
  • January 2009 (7)
  • December 2008 (21)

• AtlantaMDF Reviews

  • Kick some SSAS - Expert Cube Development with Microsoft SQL Server 2008 Analysis Services 2010-08-06
    Recently finished (cover-cover) reading of Expert Cube Development with Microsoft SQL Server 2008 Analysis Services. Since then I've gone through a number of chapters several times as topics aligned themselves with work efforts. Looking at this book today, I noticed the covers are all curled, the pages dog-eared, and yellow highlighter is visible across […]
    DBAMark
  • SharePoint 2007 Developer’s Guide to Business Data Catalog 2009-11-24
    SharePoint 2007 developer’s Guide to Business Data Catalog is an essential guide to anyone who wishes to become proficient in building complex SharePoint applications. This book starts off assuming that the reader is a complete beginner to BDC, and explains in detail what it is. Soon afterwards, the reader is taught what can be done with it and how.The book […]
    wildcat
  • The Art of Unit Testing 2009-11-08
    As a Database Architect, I often work with Object-Oriented Programmers, but thankfully don’t often have to code in other languages other than SQL.  However, it is useful to understand the methods and basic frameworks by which my team members develop; hence, I decided to review Roy Osherove’s The Art of Unit Testing (Manning Press, 2009).  Although many of th […]
    Stu

• BlogRoll