select nulls in the table [Resolved]

Hi,
comparison against NULL ( Unknown) value always returns FALSE in the WHERE condition....
This is ANSI standards...

By default, ANSI_NULLS setting is ON in SQL Server...

If you want to work only with = operator against NULL value comparison.... You set that option to OFF..

SET ANSI_NULLS OFF
select * from sys.tables where principal_id = null 
SET ANSI_NULLS ON

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

It is not so advisable to use ANSI_NULLS OFF... why because in future versions of SQL Server it always be ON. At that time the application gives error on setting ANSI_NULLS OFF as it is against the ANSI standard...

https://msdn.microsoft.com/en-us/library/ms188048.aspx

So better to use IS NULL operation instead of = operator...


If you do not want to use IS operator and must need another approach, you go ahead with ISNULL() scalar function to replace all NULL values with some default value ( which should not match with any of other values in a table to prevent logical error )

select * from sys.tables where ISNULL(principal_id, -1) = -1 -- here principal_id  -1 should not match any of other values in table  

for varchar type you replace NULL/Unknown record with 'NULL' string and then do the comparison...

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Thank u for reply...I got that.

If i set SET ANSI_NULLS OFF always in statements is there any disadvantage/Impact on query ?? If yes, please let me know where it affects

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator