SQL SERVER – Count NULL Values From Column

The other day, I have received a very simple but interesting question by one of my earlier clients of Comprehensive Database Performance Health Check via email. The question was about how to count NULL values from the column.

Honestly, this is a very simple thing to do but I can totally understand why my client was confused and here is a very simple script to demonstrate the issue of counting NULL values from Column.

First, let us create a table and populate it with a few NULL Values.

-- Create a Table
CREATE TABLE Table1 (Col1 INT);
-- Insert Data
INSERT INTO Table1 VALUES (1), (2);
INSERT INTO Table1 VALUES (NULL),(NULL),(NULL);

In the above script, we have inserted 3 NULL values.

Now run the following command to count all the NULL values from the table.

SELECT COUNT(Col1,0) CountCol
FROM Table1
WHERE Col1 IS NULL;

When you see the result of the query, you will notice that even though we have 3 NULL values the query says there are no NULL values. This is because all the aggregate values ignore the NULL values. If you want to count the NULL values, you will have to first convert the NULL values to different values and then apply the aggregate function as demonstrated in the following script.

SELECT COUNT(ISNULL(Col1,0)) CountCol
FROM Table1
WHERE Col1 IS NULL;

When you run the above script you will notice that the query is now demonstrating the correct value of NULL values. The reason for ignoring the NULL value is very simple; as this prevents unknown or inapplicable values from affecting the result of the aggregate.

However, if you have kept your ANSI Warnings on, you can find the warning in the SSMS warning message. You can read more about the ANSI Warnings over here, here and here.

Please leave a comment and let me know if you use any such tricks when you have to work with the NULL values. I personally, do not like the NULL values in my tables unless they are absolutely required. I have seen NULL creating enough confusion for the DBAs and Query Engine. However, this is the topic for some other blog posts.

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