Posted by Erland Sommarskog on 10/31/05 10:45

serge (sergea@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, esquel@sommarskog.se

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

[Back to original message]