sql server - Keeping the data of a table while adding a column -

i need add column [new_date] [date] sql table existing table [returns] , create primary keys. [returns] table has 65 million records , while repopulate of data. simplest way of doing this.

my thoughts rename table temporary name. [returns_old] , drop , create new table , assign primary key's. think joining [returns_old] [d_date] populate new table work.

assuming don't care order of columns since column order can more managed view, approach comes down whether column can nullable.

if column can null, simple statement need append column table. records null column until update them source table.

alter table [returns] add [new_date] datetime 

if column cannot null, need add few more steps.

alter table [returns] add [new_date] datetime go -- update existing records no existing value null alter table [returns] alter column [new_date] datetime not null 

if column order matter you, re-consider if @ possible (and there many reasons out there doing so). few of reasons:

• this cannot done without re-building entire table - costly move
• there no performance benefit (build indexes if necessary)
• there no storage benefit (http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/)