By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,646 Members | 1,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,646 IT Pros & Developers. It's quick & easy.

Use DEFAULT CONSTRAINTs or BOUND DEFAULTs?

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

http://groups.google.com/group/micro...c13d4ee6758966

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

Oct 30 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
"serge" <se****@nospam.ehmail.com> wrote in message
news:KQ******************@weber.videotron.net...
I am doing a little research on Google about this topic and I ran into
this thread:

http://groups.google.com/group/micro...c13d4ee6758966

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


Default constraints are closer to standard SQL and in my experience are the
more conventional solution and better understood by most people using SQL
Server today. Here's a quote from SQL Server 2005 Books Online about the
legacy CREATE DEFAULT / sp_binddefault syntax:

"CREATE DEFAULT will be removed in a future version of Microsoft SQL Server.
Avoid using CREATE DEFAULT in new development work, and plan to modify
applications that currently use it. Instead, use default definitions created
using the DEFAULT keyword of ALTER TABLE or CREATE TABLE."

--
David Portas
SQL Server MVP
--
Oct 30 '05 #2

P: n/a
On Sun, 30 Oct 2005 16:22:49 -0500, serge wrote:

(snip)
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?
Hi Serge,

I'm inclined to agree with you. I don't see how knowledge of the default
values could be of any use for the optimizer.

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.
Though it's true that NULLs should be used with care, it's also true
that they should be avoided with care. If the nature of your business is
such that you have to deal with missing data, then it's better to use
the token specifically designed for missing date (i.e. NULL) instead of
mucking around with placeholders.

How will you distinguish a bonus of 0 (we thought about it and decided
not to give a bonus) from a bonus of 0 (for any of a whole lot of
possible reasons, no information is known about the bonus at this
moment).

Also, are you aware that all these zeros and spaces will muck up you
aggregate values, and that they can impact the logic of your queries?

Finally - how will you handle datetime variables? Neither 0 nor ' ' can
be stored in them!

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
No need for that - you can include the default in the CREATE TABLE
statement.

CREATE TABLE Test
(Col1 int NOT NULL DEFAULT 0,
Col2 varchar(20) NOT NULL DEFAULT ' '
)
INSERT INTO Test DEFAULT VALUES
SELECT * FROM Test

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


Use constraints. Bound defaults are proprietary, non-portable; DEFAULT
constraints are defined in the ANSI standard. My guess is that bound
defaults are only retained in SQL Server for backwards compatibility. I
also expect this feature to become deprecated in a future version of SQL
Server.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Oct 30 '05 #3

P: n/a
serge (se****@nospam.ehmail.com) writes:
So my question is should I do this by using sp_binddefault or stick
with using Default Constraints inside a table/columns loop code?


Bound defaults and rules are great for user-defined types, and as long
Microsoft does not provide real domains, they should just not think of
dropping these constructs.

But if you are working with individual columns, there is no reason to
use bound defaults and rules. Use constraints instead.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 30 '05 #4

P: n/a
serge (se****@nospam.ehmail.com) writes:
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:


I missed this part. I completely agree with Hugo, and I even go so far
as saying that this is an extremely poor idea.

OK, so for character data, it may be difficult to find a real-world example
when NULL, empty string and a single space would really mean different
things.

But for other datatypes it's necessarily not so. In some contexts a 0
for a numeric value can serve as a NULL, because the actual domain for
the value is >= 1. Or if the domain is >= 0, you can use -1. Because that
is really what you need: an out-of-band value to represent the unknown.
For dates, there are probably a whole lot of dates you can use. But
there starts the problem. Which value did you use to represent N/A for
this column?

It gets even worse with foreign keys. In an OrderDetails table, there
is a CampaignID column for the case the customer order the product as the
result of a campaign. But what if there is no campaign. Do you add a
"NoCampaign" row to your Campaigns table? A NULL is so much easier to
handle.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 30 '05 #5

P: n/a
Hi Hugo,
Though it's true that NULLs should be used with care, it's also true
that they should be avoided with care. If the nature of your business is
such that you have to deal with missing data, then it's better to use
the token specifically designed for missing date (i.e. NULL) instead of
mucking around with placeholders.
I learned that our developers have decided not to write any ASP code
that verifies if a column is Null or not. That's why we don't leave any
columns with NULL values.

How will you distinguish a bonus of 0 (we thought about it and decided
not to give a bonus) from a bonus of 0 (for any of a whole lot of
possible reasons, no information is known about the bonus at this
moment).
This is a good example for me to keep in mind. However in my case it's
up to the developers to deal with similar cases. I also checked and we
don't seem to have any Bonus column in our database.

Also, are you aware that all these zeros and spaces will muck up you
aggregate values, and that they can impact the logic of your queries?
Can you please elaborate on this? I would like to understand how it
would impact the logic of queries?

Finally - how will you handle datetime variables? Neither 0 nor ' ' can
be stored in them!
I see we're setting them all to "getdate()".

No need for that - you can include the default in the CREATE TABLE
statement.

CREATE TABLE Test
(Col1 int NOT NULL DEFAULT 0,
Col2 varchar(20) NOT NULL DEFAULT ' '
)
INSERT INTO Test DEFAULT VALUES
SELECT * FROM Test
I will try to find out why we keep the Default Constraint statements
on separate files and not in the CREATE TABLE statements.

Use constraints. Bound defaults are proprietary, non-portable; DEFAULT
constraints are defined in the ANSI standard. My guess is that bound
defaults are only retained in SQL Server for backwards compatibility. I
also expect this feature to become deprecated in a future version of SQL
Server.


I will use constraints and forget about using bound defaults as I read also
David and Erland's answers saying to use default constraints for the
columns.

Thank you
Oct 31 '05 #6

P: n/a
> But for other datatypes it's necessarily not so. In some contexts a 0
for a numeric value can serve as a NULL, because the actual domain for
the value is >= 1. Or if the domain is >= 0, you can use -1. Because that
is really what you need: an out-of-band value to represent the unknown.
For dates, there are probably a whole lot of dates you can use. But
there starts the problem. Which value did you use to represent N/A for
this column?
Good point but like I just replied to Hugo it's not my problem. It's up
to our developers to deal with these scenarios as they are the ones
who chose not to deal with NULLs at all in their code.
It gets even worse with foreign keys. In an OrderDetails table, there
is a CampaignID column for the case the customer order the product as the
result of a campaign. But what if there is no campaign. Do you add a
"NoCampaign" row to your Campaigns table? A NULL is so much easier to
handle.


I did see some default values set to -1 so I'll look into more in this as I
am
guessing now we're using -1 to deal with this scenario you just explained.

I'll keep this in mind and as I learn more I'll see how we handle these type
of scenarios and then I'll be able to determine if we can do things
differently
and better.
Thank you
Oct 31 '05 #7

P: n/a
serge (se****@nospam.ehmail.com) writes:
I learned that our developers have decided not to write any ASP code
that verifies if a column is Null or not. That's why we don't leave any
columns with NULL values.


Then if you are the DBA, it's your responsibility to stop this insanity.
How will you distinguish a bonus of 0 (we thought about it and decided
not to give a bonus) from a bonus of 0 (for any of a whole lot of
possible reasons, no information is known about the bonus at this
moment).


This is a good example for me to keep in mind. However in my case it's
up to the developers to deal with similar cases. I also checked and we
don't seem to have any Bonus column in our database.


"Bonus" was just one example that Hugo used. There might be others.
Also, are you aware that all these zeros and spaces will muck up you
aggregate values, and that they can impact the logic of your queries?


Can you please elaborate on this? I would like to understand how it
would impact the logic of queries?


"What is the average time from order date to ship date?"

SELECT avg(datediff(HOUR, OrderDate, ShipDate) / 24.0) FROM Orders

If ShipDate is, say, 99991231, until the order has been shipped, you
will get very strange results.
Finally - how will you handle datetime variables? Neither 0 nor ' ' can
be stored in them!


I see we're setting them all to "getdate()".


What?????????????????????????

If you developers flatly refuse to deal with NULL, here is what you need
to do: move all nullable columns to their own tables, where they can be
NOT NULL.

Of course, people will need to do outer joins to get the data, in
which case the NULLs are back in town...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 31 '05 #8

P: n/a
Hi There,
You mustnot take getdate() as the default date as it will become
difficult /impossible to find which date is entered by User and which
one is not specified by the user . You must provide constant in default
constraint not anything that keeps on changing .
Alter Table ourTable Add X int Default Cast(rand()*10 as Int) ---
Completly Wrong because different values will be assigned .
Similary
Alter Table ourTable Add X1 datetime Default getdate() --- Completly
Wrong because different values will be assigned .

With Warm regards
Jatinder Singh

Oct 31 '05 #9

P: n/a
On Mon, 31 Oct 2005 00:06:38 -0500, serge wrote:
I learned that our developers have decided not to write any ASP code
that verifies if a column is Null or not. That's why we don't leave any
columns with NULL values.


Hi Serge,

In my eyes, there are now two possible options:

1. You put your foot down as DBA and insist that they start doing what
they should do. They obey (either because you convince them or because
management agrees with you) and start checking for NULL. Everyone is
happy and your company will prosper.

2. You put your foot down as DBA and insist that they start doing what
they should do. They don't obey (because they won't listen to you and
management doesn't support you). You hand in your resignation since you
can't take any responsibility for the integrity in the database. You get
a new job, and are happy. Your ex-company tumbles becasue in the end,
lack of integrity in the key database brought the company to it's knees.
(snip)
Also, are you aware that all these zeros and spaces will muck up you
aggregate values, and that they can impact the logic of your queries?


Can you please elaborate on this? I would like to understand how it
would impact the logic of queries?


Assume we're building a machine that can guess people's age by analyzing
their facial features. For a test run, we invite some test persons to
the lab, have the machine guess their age, then ask to reveal their real
age. The information is stored in the database for future anaysis.
Unfortunately, test person Kate never showed up. The machine produced an
error code instead of an age when examining Karl. And Lindy refused to
reveal her real age after the machine had made it's guess. For
completeness' sake, we do store the information we have about Kate, Karl
and Lindy; ecause our ASP developers never check for NULL, we store the
unknown ages as 0.

This is how our table looks like (use a fixed font to read):

Person | Sex | GuessedAge | RealAge
--------+-----+------------+---------
John | M | 45 | 45
Gill | F | 22 | 18
Kate | F | 0 | 0
Karl | M | 0 | 56
Lindy | F | 37 | 0
Pete | M | 28 | 26

Now write a query to find all persons where the machine guessed exactly
right:
SELECT Person
FROM TheTable
WHERE GuessedAge = RealAge
Sounds logical - but will also return Kate! The logic of the query is
impacted by the use of 0 instead of NULL.

Also, write a query to find all persons where the machine was off by
more than 3 years:
SELECT Person
FROM TheTable
WHERE ABS(GuessedAge - RealAge) > 3
Sounds logical - but will also return Karl and Lindy! The logic of the
query is impacted by the use of 0 instead of NULL.

Now, write a query to find the youngest test person (based on real age,
not on guesses) and the average guessed age:
SELECT MIN(RealAge), AVG(GuessedAge)
FROM TheTable
Will return 0 for lowest real age and 22 for average guessed age, where
I would expect 18 for lowest real age and 33 for average guessed age.
The results of the aggregate functions are mucked up by the use of 0
instead of NULL.

Finally - how will you handle datetime variables? Neither 0 nor ' ' can
be stored in them!


I see we're setting them all to "getdate()".


AAARGGGHHHH That is even worse. Setting them to a value that won't
normally be used would at least give you a small chance to even get any
useful information from your data. "Well, sir - either the applicant is
just two days old, or we entered his information two days ago but didn't
know his birthday at that time".

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Oct 31 '05 #10

P: n/a
Hi Hugo,

I actually I will elect option #3.
I work to find out where we have problems with the NULL
and AVG etc..., inform my managers and let them do
whatever they wish. I don't need to break my head after
showcasing the problems. There are other problems to discover
so I'll concentrate on the other problems.

Thanks for the detailed examples. I will look at them again
and see or find where in our application we could run into
these type of problems.

Nov 3 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.