Best Practice - Defaults on columns
I have an extensive document on Best Practices, from the DBA perspective, this is one which I don't think made it in.
I'm working with some versioning and scripting tools and the definition of column defaults in table creation scripts is giving me some pain.
If you use the method below, each time the table is created it generates a new key for the constraint, same applies to Foreign Keys and Primary Keys .. So ... DON'T DO IT !!!! it creates havoc when comparing databases and handling versioning. Naming your constraints, the second table script, is a much more DBA friendly method.
-- not good !!
CREATE
TABLE [dbo].[test2] (
[numkey] [int]
NOT NULL ,
[thedate] [datetime]
NULL default getdate()
)
ON [PRIMARY]
GO
-- Produces when scripted
CREATE
TABLE [dbo].[test2] (
[numkey] [int]
NOT NULL ,
[thedate] [datetime]
NULL
)
ON [PRIMARY]
GO
ALTER
TABLE [dbo].[test2] ADD
CONSTRAINT [DF__test2__thedate__2057CCD0] DEFAULT (getdate()) FOR [thedate]
GO
-- That's the way to do it !!
CREATE
TABLE [dbo].[test2] (
[numkey] [int]
NOT NULL ,
[thedate] [datetime]
NULL constraint DF_test2_thedate default getdate()
)
ON [PRIMARY]
GO
-- Produces when scripted
CREATE
TABLE [dbo].[test2] (
[numkey] [int]
NOT NULL ,
[thedate] [datetime]
NULL
)
ON [PRIMARY]
GO
ALTER
TABLE [dbo].[test2] ADD
CONSTRAINT [DF_test2_thedate] DEFAULT (getdate()) FOR [thedate]
GO