Why developers are using SET ANSI_NULLS ON/OFF setting in SQL Stored Procedures.

Letz understand SET ANSI_NULLS ON /OFF with an example.

CREATE TABLE dbo.Players(FirstName VARCHAR(50),LastName Varchar(50))
INSERT INTO dbo.Players VALUES(‘RAHUL’,’DRAVID’),
(‘DHONI ‘,’MS’),
(‘LAXMAN’, NULL)

SELECT * FROM Players

SET ANSI_NULLS ON
GO
SELECT * FROM dbo.Players WITH(NOLOCK) WHERE LastName = NULL
SELECT * FROM dbo.Players WITH(NOLOCK) WHERE LastName <> NULL

RESULT
FirstName LastName
-------------------------------------------------- -----------------------------

(0 row(s) affected)

FirstName LastName
-------------------------------------------------- -----------------------------

(0 row(s) affected)

SET ANSI_NULLS  ON  means we are following  ISO Standard thats the reason why = and <> is not working here.

Now Letz set ANSI_NULLS Off and then Execute the above Query
SET ANSI_NULLS Off
GO
SELECT * FROM dbo.Players WITH(NOLOCK) WHERE LastName = NULL
SELECT * FROM dbo.Players WITH(NOLOCK) WHERE LastName <> NULL

RESULT

FirstName LastName
-------------------------------------------------- -----------------------
LAXMAN NULL

(1 row(s) affected)

FirstName LastName
-------------------------------------------------- ------------------------
RAHUL DRAVID
DHONI MS

(2 row(s) affected)

see  = and <> Operator is working here .

So, when SET ANSI_NULLS is ON  we need to use IS NULL or IS NOT NULL instead of comparison operator = and <>

SET ANSI_NULLS ON 
GO
SELECT * FROM dbo.Players WITH(NOLOCK) WHERE LastName is NULL

Hope it helps.

-Rajesh

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s