SQL Server中查询至少含一个非NULL值表列的最优方法

核心思路 #

通过聚合函数的特性，仅扫描目标表1次，一次性计算所有字段是否存在非NULL值，避免逐列发起查询请求，IO开销远低于循环逐列查询的方案。

注意：你原方案中使用的COUNT(DISTINCT column_name)存在不必要的去重开销，判断字段是否全为NULL不需要统计非重复值数量，只要确认存在至少1个非NULL值即可，直接通过MAX(字段名) IS NULL判断即可，计算效率更高。

静态校验代码实现 #

如果待校验的表字段是固定的，可以直接写聚合查询语句，示例如下：

SELECT
    -- 按实际待校验字段补充即可
    CASE WHEN MAX(column1) IS NULL THEN 1 ELSE 0 END AS is_column1_all_null,
    CASE WHEN MAX(column2) IS NULL THEN 1 ELSE 0 END AS is_column2_all_null,
    CASE WHEN MAX(column3) IS NULL THEN 1 ELSE 0 END AS is_column3_all_null
FROM table_name;

校验规则：返回值为1代表对应字段全为NULL，0代表存在至少一个非NULL值。

方案优势 #

• 无论有多少个待校验字段，都仅对表执行1次全表扫描，只产生1次查询请求，IO开销和执行效率远高于逐列查询的方案
• 不需要临时表、游标等复杂逻辑，写法简单易维护
• 如果表上有列存储索引或者非聚集索引覆盖待校验字段，执行效率还会进一步提升

动态批量校验扩展方案 #

如果需要校验大量表/字段，不想手动拼接字段列表，可以通过系统表动态生成上述校验语句，示例如下：

DECLARE @table_name SYSNAME = '替换为你的目标表名'
DECLARE @sql NVARCHAR(MAX)

-- 动态拼接所有字段的校验逻辑
SELECT @sql = COALESCE(@sql + ',', 'SELECT ') 
    + 'CASE WHEN MAX(' + QUOTENAME(name) + ') IS NULL THEN 1 ELSE 0 END AS is_' + name + '_all_null'
FROM sys.columns 
WHERE object_id = OBJECT_ID(@table_name)
-- 可在此处添加过滤条件，比如排除主键、自增列等不需要校验的字段

SET @sql = @sql + ' FROM ' + QUOTENAME(@table_name)

-- 执行生成的校验SQL
EXEC sp_executesql @sql

内容的提问来源于stack exchange，提问作者Gaurav Gupta