Cuando trabajamos con SQL Server, es importante entender las diferencias entre COUNT, DISTINCT y NULLs. Estos conceptos a menudo se utilizan indistintamente para determinar el número de valores únicos en una columna, pero no siempre producen los mismos resultados.

Comencemos por ver algunos datos de prueba:

DROP TABLE IF EXISTS ##TestData;
CREATE TABLE ##TestData (Id int identity, Col1 char(1) NULL); 
INSERT INTO ##TestData VALUES ('A'); 
INSERT INTO ##TestData VALUES ('A'); 
INSERT INTO ##TestData VALUES ('B'); 
INSERT INTO ##TestData VALUES ('B');
INSERT INTO ##TestData VALUES (NULL); 
INSERT INTO ##TestData VALUES (NULL); 
CREATE CLUSTERED INDEX CL_Id ON ##TestData (Col1);

Si queremos saber cuántos valores únicos hay en la columna “Col1”, podríamos escribir una consulta como esta:

SELECT COUNT(DISTINCT Col1) 
FROM ##TestData;

Sin embargo, esta consulta puede no darnos el resultado esperado si hay valores NULL en la columna. Para ver los valores únicos reales, podemos usar la palabra clave DISTINCT:

SELECT DISTINCT Col1 
FROM ##TestData;

Sorprendentemente, la consulta COUNT(DISTINCT Col1) devolvió un recuento de dos, mientras que la consulta DISTINCT mostró tres valores únicos. Esto se debe a que la función COUNT(DISTINCT) no incluye valores NULL, mientras que la palabra clave DISTINCT sí lo hace.

Para ver los valores únicos en la columna sin incluir los NULL, podemos usar la siguiente consulta:

SELECT DISTINCT 
    Col1 
FROM 
    ##TestData 
WHERE  
    Col1 IS NOT NULL;

Por otro lado, si queremos incluir los valores NULL en el recuento, podemos usar la siguiente consulta:

SELECT COUNT(DISTINCT Col1) + COUNT(DISTINCT CASE WHEN Col1 IS NULL THEN 1 END)
FROM ##TestData;

Aunque esta lógica es fácil de entender, requiere leer los datos de la columna dos veces, lo cual puede ser ineficiente para conjuntos de datos más grandes.

Otro enfoque es usar un valor de marcador de posición que no exista en otros datos de la columna, de modo que la función COUNT lo incluya en su cálculo:

SELECT COUNT(DISTINCT ISNULL(Col1,'~~~')) 
FROM ##TestData;

Sin embargo, este enfoque puede volverse problemático a medida que aumenta el tamaño de los datos, ya que SQL Server necesita verificar cada fila y convertir cualquier NULL que encuentre.

Una opción más eficiente es usar un DISTINCT en una tabla derivada para devolver los valores NULL, y luego contar eso:

SELECT COUNT(*)  
FROM (SELECT DISTINCT Col1 FROM ##TestData) v;

Esta última opción elimina la necesidad de cálculos y ordenamientos adicionales, lo que la convierte en una mejor opción en términos de rendimiento.

Es importante entender la relación entre COUNT, DISTINCT y NULLs en SQL Server. La función COUNT devuelve elementos en un grupo, mientras que COUNT(Col1) devuelve valores no nulos en el grupo. Al usar COUNT o DISTINCT, siempre pruebe con NULLs para asegurarse de que SQL Server los maneje como se espera.