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.
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
