SQL SERVER – A Puzzle – Fun with NULL – Fix Error 8117

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)

About these ads

13 thoughts on “SQL SERVER – A Puzzle – Fun with NULL – Fix Error 8117

  1. I Did it like this

    SELECT SUM(Data) FROM (SELECT NULL+1 AS Data ) t

    it worked..Whata play with null;sir

    –Kamesh

  2. Casting null as some datatype, I didn’t knew that.

    unable to solve this puzzle first answer that came to my mind was this ;)

    SELECT SUM(data)
    FROM (SELECT 1 as data where 1=2) t

    After reading the correct answer I wont use this. Thanks Sir.

    • Hehe :)
      I had the same idea: subquery should have 0 rows, I wrote:

      SELECT SUM(data)
      FROM (SELECT CASE WHEN 0>1 THEN 1 END AS data) AS t

  3. There are some good stoies from my ANSI X3H2 committtee days, The conflict is that SQL is strongly typed and NULL offically has no type. This is why we prefer CAST(NULL AS ) to get an explicit NULL in a column.

    • Any arithmetic operation done on NULL is always NULL. Read this to know more about NUL Lhttp://beyondrelational.com/modules/2/blogs/70/posts/14865/null-null-null-and-nothing-but-null.aspx

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

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