SQL Server中如何高效统计表指定列已使用的全部字符

原实现的核心问题 #

• 循环逐位触发最多MAX(LEN(prod_name))次全表扫描，大表下IO开销极高
• 临时表逐次插入写入额外消耗tempdb资源
• 临时表用char(1)存储双字节的nvarchar字符，存在编码转换丢失风险（非ASCII字符如中文、特殊符号可能出现乱码）

通用优化方案（兼容所有SQL Server版本） #

核心思路是用系统自带的数字辅助表替代循环，仅通过一次集合关联查询完成所有位置的字符提取，无循环、无临时表写入。

SELECT DISTINCT 
    SUBSTRING(p.prod_name, n.number, 1) AS used_char,
    CAST(SUBSTRING(p.prod_name, n.number, 1) AS binary) AS char_binary
FROM Products p
-- 关联系统自带数字序列，匹配prod_name的每个有效长度位置
JOIN master.dbo.spt_values n
    ON n.type = 'P' -- 仅取纯数字序列
    AND n.number BETWEEN 1 AND LEN(p.prod_name)
ORDER BY used_char;

注：master.dbo.spt_values内置数字范围为1_{2048，针对示例中nvarchar(50)的列完全够用，若需处理更长的字符串列，可预先创建一张永久数字辅助表（生成1}N的连续整数），效率远高于循环方案。

批量适配多表多列的扩展方案 #

针对需要排查多表、多字符串列的场景，可通过配置表+动态SQL批量执行，无需为每个列单独编写查询：

-- 1. 配置需要排查的目标表、列
DECLARE @target_config TABLE (
    schema_name sysname,
    table_name sysname,
    column_name sysname
);
-- 填入实际需要排查的列，示例为产品表的prod_name
INSERT INTO @target_config 
VALUES ('dbo', 'Products', 'prod_name');

-- 2. 动态拼接批量查询SQL
DECLARE @exec_sql NVARCHAR(MAX) = N'';
SELECT @exec_sql += N'
SELECT ''' + schema_name + '.' + table_name + '.' + column_name + ''' AS target_column,
       SUBSTRING(t.' + column_name + ', n.number, 1) AS used_char,
       CAST(SUBSTRING(t.' + column_name + ', n.number, 1) AS binary) AS char_binary
FROM ' + schema_name + '.' + table_name + ' t
JOIN master.dbo.spt_values n 
    ON n.type = ''P'' 
    AND n.number BETWEEN 1 AND LEN(t.' + column_name + ')
UNION ALL'
FROM @target_config;

-- 3. 移除末尾多余的UNION ALL，去重后输出
SET @exec_sql = LEFT(@exec_sql, LEN(@exec_sql) - LEN('UNION ALL'));
SET @exec_sql = N'
SELECT DISTINCT target_column, used_char, char_binary 
FROM (' + @exec_sql + N') AS res 
ORDER BY target_column, used_char';

-- 执行查询
EXEC sp_executesql @exec_sql;

SQL Server 2017+ 额外简化方案 #

如果表数据量极大，可先将所有列的字符串聚合为单条长文本，再拆分提取字符，进一步降低关联扫描的行数：

WITH aggregated_text AS (
    -- 先把所有prod_name拼接为单个长字符串
    SELECT STRING_AGG(prod_name, '') AS full_text
    FROM Products
)
SELECT DISTINCT 
    SUBSTRING(full_text, n.number, 1) AS used_char,
    CAST(SUBSTRING(full_text, n.number, 1) AS binary) AS char_binary
FROM aggregated_text
JOIN master.dbo.spt_values n
    ON n.type = 'P'
    AND n.number BETWEEN 1 AND LEN(full_text)
ORDER BY used_char;

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