Fastest Way to Count Distinct Values in a Column, Including NULL Values Fastest Way to Count Distinct Values in a Column, Including NULL Values sql sql

Fastest Way to Count Distinct Values in a Column, Including NULL Values


SELECT COUNT(*)FROM (SELECT ColumnName      FROM TableName      GROUP BY ColumnName) AS s;

GROUP BY selects distinct values including NULL. COUNT(*) will include NULLs, as opposed to COUNT(ColumnName), which ignores NULLs.


I think you should try to keep the number of table scans down and count all columns in one table in one go. Something like this could be worth trying.

;with C as(  select dense_rank() over(order by Col1) as dnCol1,         dense_rank() over(order by Col2) as dnCol2  from YourTable)select max(dnCol1) as CountCol1,       max(dnCol2) as CountCol2from C       

Test the query at SE-Data


A development on OP's own solution:

SELECT  COUNT(DISTINCT acolumn) + MAX(CASE WHEN acolumn IS NULL THEN 1 ELSE 0 END)FROM atable