SQL SERVER – Puzzle SET ANSI_NULLS and Resultset – SQL in Sixty Seconds #052

Earlier I have posted a puzzle where I was receiving different results when I executed two different queries. I encourage all of you to read the original puzzle here, the puzzle had received many fantastic responses and I have later blogged about the solution of the puzzle over here.

Now I have decided to extend the same puzzle and take it to the next level. In earlier puzzle I had value of the ANSI_NULLS was set to ON. Now in this puzzle let us set the value of the ANSI_NULLS to OFF. When the value of ANSI_NULLS was off at that time, the result of the both the queries is almost identical. The key reason behind this behavior is ANSI_NULLS setting.

However, the puzzle is to answer that why the different queries are producing almost same results when there is a different setting of ANSI_NULL.

Let us see the same concept in following SQL in Sixty Seconds Video:

Here is the script used in this episode:

-- Original Puzzle
http://bit.ly/sql-puzzle-ansi
-- Script
SET ANSI_NULLS ON;
-- Query1
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 2
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);
-- Puzzle Solution
http://bit.ly/sql-puzzle-ansi-sol
------------------------------------------------------
-- New puzzle
SET ANSI_NULLS OFF;
-- Query3
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL);
-- Query 4
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

One thought on “SQL SERVER – Puzzle SET ANSI_NULLS and Resultset – SQL in Sixty Seconds #052

  1. When you set ANSI_NULLS off, it will not follow the ISO Standard and in comparison with column and NULL, it will always return TRUE.

    Query 3 :
    ‘Authority’ = ‘S’ OR ‘Authority’ = ‘Q’ OR ‘Authority’ = ‘L’ OR ‘Authority’ = ‘Authority’ OR ‘Authority’ = NULL

    Returns FALSE OR FALSE OR FALSE OR TRUE OR TRUE so the final result is TRUE.
    RESULT : the query will return the value SQLAUTHORITY
    ===================================
    Query 4 :
    ‘Authority’ != ‘S’ AND ‘Authority’ != ’Q’ AND ‘Authority’ != ’L’ AND ‘Authority’ != NULL

    Returns TRUE AND TRUE AND TRUE AND TRUE so the final result is TRUE.

    RESULT : the query will return the value SQLAUTHORITY

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