How To Script Default Value

Question

How To Script Default Value

Phil Stratford

SSChasing Mays

Points: 641
More actions
March 24, 2014 at 9:44 am

#396473

Can anybody explain what the difference is, if any between the following two ways of creating a table with a column that has a default value (see the 'Deleted' column):
CREATE TABLE [dbo].[Customer] (
[ID] INT IDENTITY(1,1) NOT NULL,
[Name] NVARCHAR(100) NOT NULL,
[DELETED] BIT NOT NULL DEFAULT 0)
And
CREATE TABLE [dbo].[Customer] (
[ID] INT IDENTITY(1,1) NOT NULL,
[Name] NVARCHAR(100) NOT NULL,
[DELETED] BIT CONSTRAINT [DF_Customer_Deleted] DEFAULT((0)) NOT NULL)
And is there any argument for doing one over the other?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

The main difference is that in the first statement you don't assign a name to the default constraint, so SQL Server will assign a name for it. In the second create statement you assigned a name to the constraint, so this will be the name that will be used. The constraint itself will act the same in both cases.

Personally I prefer giving names to the constraints. The 2 main reasons are:

1)If I let the server assign the name, it will be different name in each database (dev, QA, staging and production). If you'll want to write one script that drops the constraint, it will be hard to write one script that will be able to run later on all environments.

2)When I give my own name, I can make sure that I fallow naming convention and I don't have numbers in the name just to make it unique.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323518 More actions · Answer 2

it's just convenience vs completeness; ideally, you want explicitly named constraints eveywhere, but if it's not important to your schema organization, then you can allow SQL server to create names for the constraints automatically.

the named constraints are helpful when you want to alter a column...say the column changed form bit to integer...then you have to drop default constraints, then modify the column, then put the default back.

if you KNOW the name, you don't have to query the metadata and drop it dynamically.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phil Stratford SSChasing Mays Points: 641 More actions · Answer 3

Great answers, thank you. Sounds like I definitely want to explicitly name the constraints.

Do the parentheses around the default value itself not mean anything, then? In the first example there are no parentheses, but in the second there are double parentheses, which is how SSMS creates the script if I use the GUI to create the column. Why are they double parentheses..?

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

The parentheses have to be used when you have a none standard name. For example if you have space in the constraint's name. In other cases it has no purpose, but some might say that it makes the statement more readable (not me:-)).

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Phil Stratford SSChasing Mays Points: 641 More actions · Answer 5

Adi Cohn-120898 (3/24/2014)
The parentheses have to be used when you have a none standard name. For example if you have space in the constraint's name. In other cases it has no purpose, but some might say that it makes the statement more readable (not me:-)).

Thanks Adi. I was referring to the parentheses around the default value, not the constraint name. Maybe I should have called them brackets, I'm never sure about the correct terminology! I just wondered why it's ((0)) and not simply 0.

Roland C Hall of Fame Points: 3473 More actions · Answer 6

There is not any difference. Simple parentheses may help for readlibility. Double parentheses, I don't see :ermm:

The only difference I read about parentheses in DEFAULT close is that niladic functions must not be enclosed in parentheses : http://technet.microsoft.com/en-us/library/ms174979.aspx