To bind or not to bind a default to a column

Question

To bind or not to bind a default to a column

Greg Larsen

SSC-Insane

Points: 20961
More actions
December 22, 2004 at 9:28 am

#87127

There seems to be at least two different ways to assign a default value to at column. The first method is to create a default then use sp_bindefault to associate that default to a column. The second method is to issue an alter table statement that adds a default constraint to the column.
My question, is which method would be considered best practice, the sp_bindefault, or the "add constrait...default" method?

I should also mention you can define a default value on a column when you create a table by doing the following:
create table xxx(....,orderdate datetime not null (getdate()), ....)

I don't like this method because this creates a system generated constraint name.
Gregory A. Larsen, MVP
December 27, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 1

I prefer to bind the default, but I'm not sure there's a functional difference. Might be easier to read if you choose the name.

noeld SSC Guru Points: 96590 More actions · Answer 2

You don't need sp_bindefault to generate Default Constraints with a Human Readable Name FROM BOL:

ALTER TABLE MyTable

ADD AddDate smalldatetime NULL

CONSTRAINT AddDateDflt

DEFAULT getdate() WITH VALUES

You Can also separate the process of creating tables from the creating Constraints so that you have the ability you are after, like EM does

HTH

* Noel

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Hi,

Several years ago it was recommended to create Default, Constraint or Rule objects and bind them (SQL Server 7.0)

Now, with SQL Server 2000 it is recommended to use Deafaults and Constarints created inside the table by Create Table/ Alter Table statements if reasonable. Please, read Microsoft SQL Server 2000 Administrator's Companion, Chapter 16, Defaults topic, "Defining and Modifying defaults by Using T-SQL" paragraph It says:

"If you use the CREATE TABLE or ALTER TABLE method, however, SQL Server will store the DEFAULT definition with the table, and if the table is dropped, the default itself is automatically dropped as well, without your having to perform any extra steps. For this reason, it is generally recommended that you do not use CREATE DEFAULT. Using a Default object might be beneficial, however, when the same default will be used for multiple columns."

Yelena

Regards,Yelena Varsha