473,230 Members | 1,518 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,230 software developers and data experts.

Use DEFAULT CONSTRAINTs or BOUND DEFAULTs?

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
10 6926
"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
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
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
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
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
> 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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
by: Dan Cernat | last post by:
Hi there, A few threads I had a little chat about default values. I am starting this thread because I want to hear more opinions about the default values of function parameters. Some say they...
2
by: Geetha | last post by:
I have a table, tbl1: create table tbl1 ( (16) NULL DEFAULT (' '), (6) NULL DEFAULT (' ')). When I do a select * into tbl2 from tbl1, tbl2 does not have defaults. Is there any settings I...
1
by: jbtaylor16 | last post by:
I have several default constraints defined on a table. When I use the Object Browser and expand the constraints for this table and right-click and then select "Script Object to New Window As...
19
by: James Fortune | last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate...
30
by: Bruce Momjian | last post by:
I have events in the next few weeks in New York City, Copenhagen, Paris, and Atlanta. Check the News section on the web site for more information. I will also be in Amsterdam February 2-3, though...
2
by: cefrancke | last post by:
I have a datagrid on a windows form with a bindingnavigator. The tableadapter, bindingsource and dataset are there also. I can browse and edit data normally. I had added the "click" event...
2
by: Eric Bragas | last post by:
Hi, I see the following in Books Online: CONSTRAINT--Is an optional keyword indicating the beginning of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint definition... But I...
5
by: sreemati | last post by:
Hi I am working on SQL SERVER 200 and I am trying to drop the default constraints set in few tables. I tired to follow the instructions given in MSDN for dropping a default: 1) Unbind the code...
43
by: kenneth | last post by:
Dear all, I have encountered this weird problem. I have a class definition with an __init__ argument 'd' which defaults to {}. This argument is put in the 'self.d' attribute at initialization...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.