What You Don't Know
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/whatyoud
-
short and nice article
-
Nice article, but you should have added the difference between is null and = null. That is another common mistake that I see a lot in code.
Another, even harder to track down problem, is @vara != something. If @vara is null, then you will get a positive result. This should be dealt with separately.
-
ISNULL is definitely the way to go. Stay away from set concat_null_yields_null. There is a bug in SQLServer that causes stored proc's & triggers to recompile and cause contention b/c these compiles are serial and a compile lock is obtained each time a trigger or proc must be recombiled. We saw a major performance hit with this and have spent the last year or so removing set concat_null_yields_null from all of our db objects and using the isnull operator to replace it.
-
Good article. Maybe another bit of syntax to mention is how to deal with aggregates. e.g. SUM(Amount) vs SUM(ISNULL(Amount,0)) vs ISNULL(SUM(Amount),0).
-
Good article. Two typos that I noticed. One minor, one not so minor:
Minor: Use IsNull (set @SomeVar = IsNull(@SomeVar, '') + Isnull(@SomeVar2, '') should have an additional 
at the end.Not so minor: Yet I continue to see code that does plan for the possibility of nulls. Should be: Yet I continue to see code that does NOT plan for the possibility of nulls.
-SQLBill
-
I kept waiting for the COASLESCE function in this article! Where is it, lol?
John Scarborough
MCDBA, MCSA -
Nice, to the point... always a good "reminder". Seems coding for NULLs is like making backups, as soon as you don't, you get bit
Once you understand the BITs, all the pieces come together
-
IMO
First, ideally each table column should explictly define NULL or NOT NULL nullability attribute.
Second, queries may have to deal with several servers who nullability attributes are not all defined the same. However, this scenario may require the use of connection options in order to get behaviour which allows the application to succeed (meaning each server could have nullability attributes implemented differently for its default behaviour.
Third, connection options can affect NULL behaviour. Again, ideally, rely on default behaviour. On a short-term basis, connection options may need to specify expected behaviour to prevent massive application conversions.
Fourth, maybe NULLability attributes should NOT be a behaviour which can be changed/modified in a future release of SQL Server software.
Bottom line for now: It seems to me that we should learn the default behavior for NULLs and program accordingly.
None of the above addresses the situtation where we have linked servers (i.e. Oracle, Sybase, etc.) whose NULLability handling may not match SQL Server.
This seems to argue for a single ANSI-92+ standard for handling NULLS:
1. Enforce each column definition to define Null or Not Null
2. And I would argue for a non-modified option of handling Nulls
3. Make the language handle "= null" and "IS NULL" the same (eliminate confusion or ambiguity) .
-
Nice introduction to the concept of NULL is unknown. But I disagree with the implication that NULL should be treated as some default value. Yes sometimes treating @variables as defaults values when NULL is encounterd is important. But, this point totally begs the question of why sql server supports NULL at all.
In general NULL(UNKNOWN) operator VALUE = VALUE is just plain broken and leads to confusion. This construct implies I take the operator at hand and replace the NULL values with identity default values such that NULL becomes 0 under addition so the NULL + VALUE = VALUE. But consider what this approach would mean under multiplication. NULL * VALUE = VALUE, because now NULL defaults to 1 for multiplication and 1 * VALUE = VALUE. Now what happens when the other operand is NULL. NULL * NULL = 1. Hmm seems like that's a path that T-SQL expressions shouldn't support.
There are plenty of options for working with NULL valued data and plenty of data oriented reasons for wanting to allow NULLs. ISNULL is just one. NULLIF, COALESCE, IS NULL, IS NOT NULL, OUTER JOINS, etc. are others.
Peter Evans (__PETER Peter_)
-
I agree the assumption of a specific value is incorrect if you need to support the unknown. FOr example we have a system which has schedules that become active at a point in time and end at a point in time or are null. The reason is you can have an alternate schedule sometimes and NULL is assumed beyond that time frame if the permanent schedule is replaced with a new permanent then we write the date to the original it ends (the value is now known). However for some calculations I want it understood as past any alternate so I use ISNULL to assign a defualt to provide that look only. If I want to know what permanent schedules there are I can just look at IS NULL on that field.
Assuming a value thou on everything in every circumstance cn lead to reporting errors and thus the unknown is the best friend you can have. Besides if you choose a value to represent NULL will it always be remembered when you leave even if you have documentation, some people don't look. NULL is universal to folks as unknown and does not create confusion as much as one would think except to the non-programmer who never deals with it.
-
Remarkable article sir! do you have any more useful article like this ? -- kindly share with us.
Hari
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply