SQL Server - Treat Null Values in Count() function

As we know that the COUNT function can tell us the total number of rows returned in a result set (both NULL and non-NULL together but it is also depending on how it’s used). 

For example, 

• Using SELECT COUNT(*) or SELECT COUNT(1)  (which is what I prefer to use) will return the total of all records returned in the result set regardless of NULL values. 

This is because, we are not specifying any expression in the count function. So, this function is returning all the rows including null values also.

• Using COUNT (<Specific Column Name Here>) will count the number of non-NULL items in the specified column (NULL fields will be ignored).
• COUNT (<expression>) counts rows where the <expression> is not null.

Thus, we could find the number of NULL fields in the result set by subtracting the non-NULL fields from the Total fields. 

Note: In SQL Server, since the COUNT (and other aggregate functions) will ignore NULL values, we use the CASE to turn NULLs into values and values into NULLs

In the below example, we are creating a data-set with the help of common table expression and using the count function -

--This will return count of 2 ;WITH CTE     AS (     SELECT NULL AS Id     UNION ALL     SELECT 1 AS Id     )     SELECT COUNT(*) FROM CTE --This will return count of 1 ;WITH CTE     AS (     SELECT NULL AS Id     UNION ALL     SELECT 1 AS Id     )     SELECT COUNT (Id)  FROM  CTE

 So, it depends on you, how can you use count function in your SQL scripts.