3

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.

3 Answers 3

3

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.

4

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.

5
  • 1
    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. Commented Jul 29, 2014 at 16:12
  • 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). Commented Jul 29, 2014 at 20:44
  • @KennethFisher you're right Commented Jul 30, 2014 at 6:12
  • @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. Commented Jul 30, 2014 at 6:33
  • 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. Commented Jul 30, 2014 at 22:28
3

Simply use the following.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.