Home > T-SQL > SET ANSI NULLS – SQL Server – SET Statements

SET ANSI NULLS – SQL Server – SET Statements


SET ANSI_NULLS: It turns the ISO compliant for null comparison ON or OFF. If we set this setting to on then the select query that uses comparison operator = and <> will treat NULL as unknown value. So, it will not validate the null value in the column. If it is off then it treats null value as null and we can use = or <> operators to check for null value in the column.

ANSI_NULLS setting does not apply to IS NULL or IS NOT NULL in the where clause

Implementation:

I have a table with few rows as shown below.

pic2There are 3 null value in this table’s column “Population”. I am going to set ANSI_NULLS ON and print the rows in the table which as null value in population column.

pic2

We have got zero rows. Now, I am going to execution the same query with the ANSI_NULLS OFF.

pic2

Now, we got 3 rows in the result.

Another experiment for you. Try using <> NULL in the where clause.

We have learned how the use of SET ANSI_NULLS  in this blog post.

Note: In a future version of SQL Server, ANSI_NULLS will always be ON. So, avoid using this in your new project.

Advertisements
Categories: T-SQL Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: