Just got a question on my A first look at sequences in SQL Server Denali post.
The question is the following: How can you add a new column to existing table (already populated) and make it a sequence column?
Let’s take a look. First create this very simple table
CREATE TABLE bla (id INT) INSERT BLA VALUES(1) INSERT BLA VALUES(2) INSERT BLA VALUES(3) INSERT BLA VALUES(4) INSERT BLA VALUES(5)
What you want to do is have the sequence start at 6, here is how to do that.
CREATE SEQUENCE GlobalCounter AS INT MINVALUE 1 NO MAXVALUE START WITH 6; GO
Use START WITH to indicate that the sequence should start at 6
The next step is to add a default to the column, this default would use the sequence
ALTER TABLE bla ADD CONSTRAINT id_default_sequence DEFAULT NEXT VALUE FOR GlobalCounter FOR ID;
Now if we do an insert, followed by a select
INSERT bla DEFAULT VALUES SELECT * FROM Bla
Here are the results
As you can see the value 6 was inserted
There you have it, a simple way to change a column to a sequence column