Adding an identity column or column with a default constraint to a table that already has data

Today I had a coworker ask me to review code that had a select statement moving data out of a table, truncating the table, adding an new column with identity and a default constraint, then re-inserting the data into the table. His explanation was that he couldn't add an identity column or a column with a default constraint to a table that contained data.

I displayed to him that moving data out of the table isn't needed to add either a default constraint or identity.

Here's an example of how to do that:

Create a table containing existing data:

create table Customer (
Firstname nvarchar(200)
, Surname nvarchar(200)
)

insert into customer(
firstname
, surname
)
select 'Bill', 'Palmer'
union all select 'Sarah', 'Moulton'
union all select 'Wes', 'Davis'

select firstname
, surname
from customer

There's no problem with adding an identity column to an existing table, except perhaps the time it takes to run this statement - keep that in mind if you're dealing with a very large table.

alter table customer add id int identity(1,1)

select id
, firstname
, surname
from customer

That query will succeed, and return the results of a select statement that includes the new ID column, with values!

Adding a column with a default constraint to an existing table requires a little trick. For this example, let's assume we want to add a bit column to customer, indicating whether the customer is a VIP. 

If you do the usual command to add a new column with a default constraint, as below, you may be surprised.

alter table customer add isVIP bit default(0)

select id , firstname , surname , isVIP
from customer

Adding the default constraint will succeed, but the returned result-set will have null values in the "isVIP" column for the existing rows. To populate existing rows with the default value, run this statement instead:

alter table customer add isVP bit default(0) not null

select id
, firstname
, surname
, isVP
from customer

The existing rows with be populated your default value, and return that value in your result-set. 

If you want this column to allow NULL values, run the below statement to set the column to nullable, keeping the recently created default constraint.

alter table customer alter column isVP bit null