select nulls in the table [Resolved]

Posted by Sriharim under Sql Server on 7/13/2015 | Points: 10 | Views : 689 | Status : [Member] | Replies : 3
select * from sys.tables where principal_id is null

however, select * from sys.tables where principal_id = null is not working...

i want re-write above query using '=' instead of 'is'

How it is ?

---
Srihari



Responses

Posted by: Bandi on: 7/13/2015 [Member] [MVP] Platinum | Points: 50

Up
1
Down

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

Posted by: Bandi on: 7/13/2015 [Member] [MVP] Platinum | Points: 50

Up
1
Down

Resolved
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

Posted by: Sriharim on: 7/13/2015 [Member] Starter | Points: 25

Up
0
Down
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

---
Srihari

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

Login to post response