Posted by serge on 10/31/05 07:06

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

[Back to original message]