SQL SERVER – A Puzzle – Fun with NULL – Fix Error 8117
- by pinaldave
During my 8 years of career, I have been involved in many interviews. Quite often, I act as the interview. If I am the interviewer, I ask many questions – from easy questions to difficult ones. When I am the interviewee, I frequently get an opportunity to ask the interviewer some questions back. Regardless of the my capacity in attending the interview, I always make it a point to ask the interviewer at least one question.
What is NULL?
It’s always fun to ask this question during interviews, because in every interview, I get a different answer. NULL is often confused with false, absence of value or infinite value. Honestly, NULL is a very interesting subject as it bases its behavior in server settings. There are a few properties of NULL that are universal, but the knowledge about these properties is not known in a universal sense.
Let us run this simple puzzle. Run the following T-SQL script:
SELECT SUM(data)
FROM (SELECT NULL AS data) t
It will return the following error:
Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.
Now the error makes it very clear that NULL is invalid for sum Operator. Frequently enough, I have showed this simple query to many folks whom I came across. I asked them if they could modify the subquery and return the result as NULL. Here is what I expected:
Even though this is a very simple looking query, so far I’ve got the correct answer from only 10% of the people to whom I have asked this question. It was common for me to receive this kind of answer – convert the NULL to some data type. However, doing so usually returns the value as 0 or the integer they passed.
SELECT SUM(data)
FROM (SELECT ISNULL(NULL,0) AS data) t
I usually see many people modifying the outer query to get desired NULL result, but that is not allowed in this simple puzzle. This small puzzle made me wonder how many people have a clear understanding about NULL.
Well, here is the answer to my simple puzzle. Just CAST NULL AS INT and it will return the final result as NULL:
SELECT SUM(data)
FROM (SELECT CAST(NULL AS INT) AS data) t
Now that you know the answer, don’t you think it was very simple indeed? This blog post is especially dedicated to my friend Madhivanan who has written an excellent blog post about NULL. I am confident that after reading the blog post from Madhivanan, you will have no confusion regarding NULL in the future. Read: NULL, NULL, NULL and nothing but NULL.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
