Sql-server – Adding non-nullable NEWSEQUENTIALID() column to existing table

alter-tablesql-server-2008uniqueidentifier

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.

Best Answer

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.

Related Solutions

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Your problem is that when you add a new column to the_person, its child, the_person_two will have this field appended at the end of columns list (4th position), so after has_default column. See:

db=> \d temp_person
  Column   |       Type        |                            Modifiers                            
-----------+-------------------+-----------------------------------------------------------------
 person_id | integer           | not null default nextval('temp_person_person_id_seq'::regclass)
 name      | character varying | 
 foo       | text              | 

db=> \d temp_person_two 
   Column    |         Type         |                            Modifiers                            
-------------+----------------------+-----------------------------------------------------------------
 person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)
 name        | character varying    | 
 has_default | character varying(4) | not null default 'en'::character varying
 foo         | text                 |

So, when you execute this:

INSERT INTO temp_person_two VALUES ( NEW.* );

PostgreSQL will actually understand that you want to insert on the first three columns of temp_person_two (as NEW.* will expand to three values), generating something similar to this:

INSERT INTO temp_person_two(person_id,name,has_default)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

So, temp_person_two.has_default will get the value of NEW.foo, which is NULL in your case.

The solution is to simply expand the column names:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

or, you could also use this:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.* );

But this is weak, as any changes on column positions may break your statements, so I'd recommend the first one.

EDIT:

So the conclusion and the lesson learned here is:

Always explicitly type the names of the columns and the values when issuing an INSERT command, in fact, when issuing any SQL command at all... =D

This will save you a lot of time solving problems like that in future.

Mysql – Best way to add a new column to a large table? (MySQL – MyISAM)

To make online changes to a table you can use pt-online-schema-change

    pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor

The table will be blocked for a few seconds when it switches new and old tables. The table though must have a primary keys and not have triggers.

Best Answer

Related Solutions

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Mysql – Best way to add a new column to a large table? (MySQL – MyISAM)

Related Question