Posted by serge on 10/30/05 23:22

I am doing a little research on Google about this topic and I ran into
this thread:

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/63cce060ff152dcc/1dc13d4ee6758966?lnk=st&q=difference+constraint+sql+defaults&rnum=14#1dc13d4ee6758966

I read SQL Server MVP Louis Davidson's post saying:

"Actually they are more likely to drop the concept of bound defaults.
Constraints are the standard way to do this, and really should be the way
you create defaults anyhow."

Even I read in the Microsoft SQL Server Introduction (SQL 7 book
page 244, however we're using SQL Server 2000):

"Constraints define rules regarding the values allowed in columns and are
the standard mechanism for enforcing integrity, preferred over triggers,
rules, and defaults. They are also used by the query optimizer to improve
performance in selectivity estimation, cost calculations, and query
rewriting."

Why constraint defaults are better? The second sentence about constraints
having better optimization, I am guessing they don't mean this about
Default Constraints, rather the other type of constraints?
Because I don't see how a Default Constraint have anything to do with
performance? Isn't default only to do with new records being created?


At work we are setting all tables' columns to have constraint defaults
of 0 or ' ' (space character) in order not to have any column with the
NULL value. Therefore we have dozens of files containing statements like:

alter table TABLE1 add constraint TABLE1_ID_DF
DEFAULT(' ') FOR ID
go
alter table TABLE1 add constraint TABLE1_QUANTITY_DF
DEFAULT(0) FOR QUANTITY
go

First I was thinking to create 3 SQL Defaults called:
DefaultZero
DefaultSpace
DefaultDate

and then bind these defaults to all the columns of all tables excluding
primary keys. After creating the tables I would enumerate through
all the columns and bind one of these three Defaults based on their
datatype:
number = DefaultZero
text type = DefaultSpace
date type = DefaultDate

And then unbind the ones that we specifically need to specify other
default values.

So my question is should I do this by using sp_binddefault or stick
with using Default Constraints inside a table/columns loop code?


Thank you

[Back to original message]