Database entirely NOT NULL | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Database entirely NOT NULL

Hi, I am joining a team. The project architech seems to go with a database with 100% NOT NULL columns. When inserting data, unknown value would be replaced by an arbitrary value like empty string or -1, etc. The goal is to simplify the programming as there is no NULL to worry at the client code side. I found this weird and argued that an arbitrary value is already meaningful and the extra constraints we add to avoid NULL might end up to be havier than handling the NULLs. At any rate I have never seen such an "extreme" design. Can you please advise if it is OK to go with 100% NOT NULL. And possibly the reason why. The database currently has 30 tables. The tables themselves are well designed and normalized. Thanks you very much in advance.
In can’t find a good reason to do that.
What simplify programming means?. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
My advice is not to do this. No way to differentiate between a blank string an unspecified one.
This is lazy programming imo, but not everyone agrees, some people advocate eliminating NULLS. Heres a good thead on the subject http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5943
The idea and best practice with NULL is to avoid wherever possible through normalization of design. At implementation, it can make sense in some cases, such as expiration dates. You can then test for &gt; 1/1/1901 for example instead of &gt; blah and IS NOT NULL; however, in some cases you should use NULL. For example as a birthdate, if the customer has not provided the birthdate, it indicates that it is indeed unknown or NULL. The arguments run long and weary on both sides of the fence; and it’s hard to give a definitive answer either way. This might give you something to think about. <br /><br />We’ve had this discussion a few times on www.sqlteam.com if you want to read for hours and hours. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Database features tend to be provided for a reason, using NULL in the data if the value is unknown/not specified/not relevant is better than substituting some other value. What if this new chosen value becomes a valid value later on? If you really wanted to then you can always use a presentation layer set of stored procs which mask out the nulls to the chosen ‘like null’ values. I agree with Chappy that this tends to be done due to lazy programming rather than actual business/technical reasons Cheers
Twan
This is an almost religious question that neither Codd, nor Date nor anybody else will solve ultimately. There are pros and cons for each approach. All sound reasonable and sane. So if that is the policy in your shop, it’s okay. This is definitely not how I see things, but I wouldn’t fight this war as there will be no winner.
———————–
–Frank
http://www.insidesql.de
———————–

I’ll copy my reply from another thread about this:

I try and stay away from nulls due to developers putting different meaning on null. Is it an unknown value, is it nothing, is it a missing value or is it a false value? For me it’s an unknown value but that’s not how everybody see it. Then you have practical stuff like doing a COUNT(*) where you have null values. People who don’t understand how it works with null will run into things like:
select count(*) from my table -> 1000 rows
select count(*) from mytable where = mycolumn = ‘x’ -> 200 rows
select count(*) from mytable where = mycolumn <> ‘x’ -> 0 rows the missing valuse are in select count(*) from mytable where mycolumn is null. So it’s not that I have something against null that I stay away from it but practically when working with many developers it simple becomes a real hassle when everybody don’t have the same view of nulls.
— But if your team define how NULLs should be used and you all agree on ANSI NULL settings etc in advance there should be less issues.
Thank you very much for all of your advices. Your answers and related links have provied me with all the information I was looking for. @LuisMartin: What simplify programming means? things like not worry to check for null (eg: WHERE email = ” OR email IS NULL) exactly like what Argyle and derricklegget have explained. Or wondering about specific NULL operation like (any Value) + null = null. @derrickleggett: thanks for the specific details. Me too, I didn’t find obvious to select a default value for datatype other than string. Because this could lead to misleading results in case of aggregate operations.

Hi Argyle,
having nulls when you have numeric data does make more sense though… afterall doing things like average, min and max, etc. get skewed if you don’t use NULL for an unknown value…? Cheers
Twan
Yea you can’t avoid it everywhere.
]]>