Adding non-nullable NEWSEQUENTIALID() column to existing table

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.

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.

StackExchange Database Administrators Q#72604, answer score: 4