Search code examples
sqlsql-serverdefault-value

Simply setting a DEFAULT vs setting a CONSTRAINT on a column in SQL Server

I'm almost new at SQL Server programming. Today I have found these two different strategies to define a default value for a column, I would ask the experts the difference between them.

Given this table:

CREATE TABLE [dbo].[Data] 
(
    [RecDataW] [datetime] NULL
)

Snippet #1:

ALTER TABLE [dbo].[DatiSetup] 
    ADD DEFAULT (GETDATE()) FOR [RecDataW]
GO

Snippet #2:

ALTER TABLE [dbo].[DatiSetup] 
    ADD CONSTRAINT [DF_DatiSetup_RecDataW] DEFAULT (GETDATE()) FOR [RecDataW]
GO
Solution

  • The difference is assigning an explicit, known name. The form

    ALTER TABLE [dbo].[DatiSetup] 
    ADD DEFAULT (GETDATE()) FOR [RecDataW]

    will create a default with a name, but the name is system generated, and will be different in each environment the script is deployed to. (Dev, test, UAT, staging, production, Bob's desktop, etc.) If you need to alter the default, this can be a problem.

    The form

    ALTER TABLE [dbo].[DatiSetup] 
   ADD CONSTRAINT [DF_DatiSetup_RecDataW] DEFAULT (GETDATE()) FOR [RecDataW]

    explicitly names the default, so that the name is uniform in all environments. So one has a way of referencing, in all environments, in a repeatable way, the default if it needs to be dropped, altered, etc.

    The standard of the places I have worked for the last few years is to always name constraints. [Sometimes that's the standard, because I have the authority to make it the standard. Sometimes someone else has already made it the standard.]

    Also, there are ways of developing and deploying SQL where the first system generated name is used on subsequent environments. The tools used explicitly name the constraint downstream to match. Red Gate Sql Source and Sql Compare can do this. Even so, name all the constraints.