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
Related Tips in SQL in Sixty Seconds:
- SQL SERVER – SQL Puzzle of SET ANSI_NULL
- SQL SERVER- Solution – SQL Puzzle of SET ANSI_NULL
- QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation
- Restrictions of Views – T SQL View Limitations
What would you like to see in the next SQL in Sixty Seconds video?
Reference: Pinal Dave (https://blog.sqlauthority.com)