We have all written SQL scripts to create a table that has one or more column defaults, as follows:
create table MyTable
(
myTableId int primary key identity(1,1),
name varchar(100) not null,
modifiedOn datetime default(getdate()),
someOtherDate datetime
)
That works well, but in some cases, you want to add a default after the fact, and you don't want to drop and re-create the table with the new default (because there is data in the table). I had this situation today so I decided to load up SQL Books Online and see if I could figure out how to write a SQL script to add a default to an existing table. (I prefer to keep incremental schema change scripts rather than making schema changes in SQL Server Management Studio, or Enterprise Manager in this case, because it makes it easier to keep multiple environments/DBs in sync.) I would have expected the syntax to be something like this:
alter table MyTable add default df_modifiedOn to someOtherDate 'getdate()'
...but it's nothing of the sort. The syntax is a bit weird, IMO, and here it is:
create default df_someOtherDate AS 'getdate()'
GO
sp_bindefault df_someOtherDate, 'MyTable.someOtherDate'
GO
I'd be interested to know if there's other syntax out there for accomplishing the same goal in one SQL statement. (Post a comment!)
That's all folks!
Update: As it turns out, the type of default created by using using "create default" is a different sort of default than the one you get by specifying "default" in a column definition, and the type created by "create default" is only maintained in SQL Server for backwards-compatibility. (In other words, don't do it unless you absolutely have to.)
I removed the default I created using the above method (using sp_unbindefault and then 'drop default') and then I created a new default by dropping and re-adding the column (so I guess my initial instinct on how to do this was almost correct).
alter table MyTable drop column someOtherDate
GO
alter table MyTable add someOtherDate datetime not null default(getdate())
GO
As it turns out, the data in this particular column is not valuable to me at this point, so dropping and re-adding the column is an acceptable solution. If you needed to preserve that data, you could dump it into a temporary table before dropping and re-adding, and then dump it back into your regular table from the temporary table. Is there a simpler way to do this?