Hey Folks,
You might be afraid of using NULLS in your programming code because nulls have such a devastating effect on expressions; some developers avoid using of nulls.
They develop their databases as if the NULLS are never permitted, and column defaults supply surrogate nulls like Blanks, 0, or ‘N/A’ instead.
NULL indicates that the data has not been entered into the database or that the column does not apply to the particular row. Since NULL values are unknown or “value absent”, the result of any expression that includes null will also have a value that is unknown.
NULLS are valuable in a database because they provide a consistent method of identifying missing data and certain queries also produce Nulls in the results, so it’s worth writing codes that checks for Nulls and handle them appropriately.
In SQL Server, one of the advantage of using Nulls is that the functions like AVG () and COUNT () aggregate functions automatically exclude Nulls from their calculation. But if we use a surrogate nulls then every aggregate query must have to filter out the surrogate null or the results will be less than accurate.
Testing for NULL:
- This Test shows that Null does not equals Null:
IF NULL=NULL SELECT 'EQUAL'; ELSE SELECT 'NOT EQUAL';
The result set can be seen as:
- Since the operators like ‘=’ and ‘<>’ can’t be check for Nulls, SQL includes two special operators for this purpose: IS and IS NOT. For e.g., using the IS operator:
IF NULL IS NULL SELECT 'RIGHT'; ELSE SELECT 'WRONG';
The result can be seen as:
You can see that IS proves to be a valid operator.
- It will be more clear, if you go through this example, here we are finding out those students whose State is NULL:
USE TEST SELECT Name,City,State FROM dbo.Students2 WHERE State IS NULL ORDER BY Name;
The result will be displayed as:
- Now we will see how to use the IS NOT operator quickly by going through an example:
SELECT * FROM dbo.Students1 WHERE City IS NOT NULL
The result will be displayed as:
Well I think this might be enough for this post, in the next blog post I would like you to tell how to Handle the NULLs and many more…
Hope you like my post 🙂
So Stay Tuned !
Regards
Piyush Bajaj
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
“Since the operators like ‘=’ and ‘’ can’t be check for Nulls….”
The part I have quoted above from your blog is not totally true.By setting up Set Ansi_Nulls Off you can use use comparison operators to compare 2 NULL’s .
1 Set Ansi_nulls Off
2 Go
3 Select 1 Where NULL = NULL
There is a lot of confusion in the SQL Server team itself on ways to handle Null values.Really cannot believe the solution they came up with….Depreciating the setting of Ansi_Nulls to Off in next version.
Also the NULL values behave wierdly when you have a unique constraint. A column having a unique key will not allow multiple NULL values while in the above example it does treat two NULL’s as equal.
Strange…Isnt it !!!!
Well Sachin……using “Ansi_nulls” is not a good option as you can refer to the MSDN link : http://msdn.microsoft.com/en-us/library/ms188048.aspx
As it has been added to their deprecated list, so it’s better if we stop using this operator anymore.
Anyway thanks for the information sharing.. 🙂
Well not sure how did Microsoft suddenly woke up in 2011 after almost 20 years of SQL Servers first release to understand setting Ansi_Nulls is not a good option…Undecided
Actually, Microsoft woke up to see that clearing the ANSI_NULLS option is not a good option. ANSI_NULLS will be the only option. If two values are unknown, there is simply no way to tell that they are equal, nor different. Comparing unknown values simply doesn’t make any sense at all.