Adding non-nullable NEWSEQUENTIALID() column to existing table

Question

In SQL Server 2008, I am adding a non-nullable column to an existing table, and want to make the default NEWSEQUENTIALID().

ALTER TABLE MyTable 
ADD MyColumn UNIQUEIDENTIFIER NULL
CONSTRAINT DF_MyTable_MyColumn DEFAULT NEWSEQUENTIALID()
UPDATE MyTable SET MyColumn = NEWID()
ALTER TABLE MyTable
ALTER COLUMN MyColumn UNIQUEIDENTIFIER NOT NULL

Is it safe to populate the column with NEWID() as shown above, or is there a better way to use a sequential ID value?

My concern is that NEWID() will generate a new guid that may be close to the sequential guid. Then when the sequential guid hits that value from NEWID(), it will run into issues.

Answer 1

I tried what you are doing but unfortunately it did not give me sequential IDs. I initially tried updating with NEWSEQUENTIALID() but it isn't allowed. Try using the DEFAULT keyword instead.

UPDATE MyTable SET MyColumn = DEFAULT

Here is the test I did to confirm that the GUIDs were sequential:

CREATE TABLE MyTable (id int not null identity(1,1))
GO
INSERT INTO MyTable DEFAULT VALUES
GO 1000
ALTER TABLE MyTable 
ADD MyColumn UNIQUEIDENTIFIER NULL
CONSTRAINT DF_MyTable_MyColumn DEFAULT NEWSEQUENTIALID()
GO
UPDATE MyTable SET MyColumn = DEFAULT
GO
ALTER TABLE MyTable
ALTER COLUMN MyColumn UNIQUEIDENTIFIER NOT NULL
GO
SELECT * FROM MyTable ORDER BY MyColumn 

I did a quick scan of the results and my id column was all in numeric order.

Answer 2

1) there is no need to add a NULL-able column, update, then alter it. It can be done in one pass using the WITH VALUES syntax (see http://sqlfiddle.com/#!3/5e1dea/2):

ALTER TABLE MyTable 
ADD MyColumn UNIQUEIDENTIFIER NOT NULL
 DEFAULT NEWSEQUENTIALID()
WITH VALUES;

2) Use of NEWID() vs NEWSEQUENTIALID() is a moot point if you do it in one pass. But for the sake of the argument, lets see what happens. The use of sequential GUIDs is only relevant if you index the column. Because random GUIDs are so random, inserting new values results in frequent page splits. For a clustered index column this can be really problematic. But you are not updating an indexed column, so the point is irrelevant. Had you add an nonclustered index later, the index builder would first sort the column then build the index. Afterwards new inserts would find a spot in the table where the sequential GUIDs would happen to sort between the random GUIDs and the start growing the table from there. This would be basically a tail-page split (even though is not a tail-page) so it would still be OK.

Overall I think the use of WITH VALUES syntax (which is surprisingly little known) renders your question obsolete. I don't think you had to do the ALTER in three steps.

• Kenneth Fisher: I played with a bit more and surprisingly the WITH VALUES isn't needed. If the column is NOT NULL and there is a default the default value is filled in. WITH VALUES is only required for NULLable columns to force them to fill in the default values.
• Greenstone Walker: Sequential GUIDS are only relevant if that column has a clustered index on it. A nonclustered index does not "sort the column" - it duplicates the indexed data into the leaf pages and then sorts those leaf pages. Page splits on index leaf pages have less implication than page splits on data pages. (Also, it is "moot point" - a point that requires debating in a moot, or assembly).
• Remus Rusanu: @KennethFisher you're right
• Remus Rusanu: @GreenstoneWalker the index builder does sort. Easily tested with a plan, eg. gist.github.com/rusanu/bd478cf99d472937375e <CreateIndex>...<Sort>.... But thanks for the vocabulary correction.
• Greenstone Walker: Sorry about that, my comment is badly written (possibly I was confusing this question with another question about NEWSEQUENTIALID). What I'm trying to say is that I feel it is important to distinguish between the the data pages (not sorted) and the leaf pages (sorted) of a nonclustered index. Inserts and updates may cause page splits in the leaf pages, but not in the data pages.

Answer 3

Simply use the following.

ALTER TABLE MyTable 
ADD 
 MyColumn UNIQUEIDENTIFIER NOT NULL
 CONSTRAINT DF_MyTable_MyColumn DEFAULT ( NEWSEQUENTIALID() ) ;