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.
Let me know if I can run more tests for you (SQL Server 2005, SP3) I got the following, looks generally a lot like what you got…
ID Marker
254D5FD2-EDD7-4EEB-9DC5-E66F71303B22 4
CB761820-64D2-4D63-A129-CC19500FCE89 10
0B014BF1-0F6A-4A77-9954-C249ACEC6170 3
082455F8-1B15-40A3-87F7-BA01924D9ADC 5
B2102714-B2D5-44DB-AC87-83E861F0A723 1
3D5DA1AC-4900-4531-850C-65DEC8209645 6
5E9D5FCE-B466-4F6D-86A9-657BB243BE58 7
85063640-5F28-4A5C-8158-52F509AE0F99 9
79CB7C15-3FBC-480A-8CDA-15DECB33B9B6 2
48C4DF67-79D6-4E43-8040-087415A98423 8
8FA1928A-2691-DE11-AA83-0019D12E68DF 20
8EA1928A-2691-DE11-AA83-0019D12E68DF 19
8DA1928A-2691-DE11-AA83-0019D12E68DF 18
8CA1928A-2691-DE11-AA83-0019D12E68DF 17
8BA1928A-2691-DE11-AA83-0019D12E68DF 16
8AA1928A-2691-DE11-AA83-0019D12E68DF 15
89A1928A-2691-DE11-AA83-0019D12E68DF 14
88A1928A-2691-DE11-AA83-0019D12E68DF 13
87A1928A-2691-DE11-AA83-0019D12E68DF 12
86A1928A-2691-DE11-AA83-0019D12E68DF 11