SQL Server：从百余个传感器数据表采集数据的高效方案问询

哥们，完全同意你的看法——写100+个UNION ALL不仅写的时候崩溃，后续维护新增传感器表更是噩梦，性能还不一定最优。给你几个实用的方案，按你能接受的改造程度来选：

方案1：动态SQL自动生成查询 #

这是最快速的临时解决方案，不用手动维护一堆UNION ALL，还能自动适配新增的传感器表。

核心思路是利用SQL Server的系统视图（比如sys.tables或INFORMATION_SCHEMA.TABLES）获取所有传感器表的名称，然后自动拼接成带UNION ALL的查询语句。如果你的传感器表有统一命名规则（比如前缀Sensor_），过滤起来更方便。

示例代码：

DECLARE @sql NVARCHAR(MAX);

-- 自动拼接所有传感器表的查询，添加SensorID标识数据来源
SELECT @sql = STRING_AGG(
    CONCAT('SELECT SensorID = ''', name, ''', * FROM ', QUOTENAME(name)),
    ' UNION ALL '
)
FROM sys.tables
WHERE name LIKE 'Sensor_%'; -- 根据你的表名规则调整过滤条件

-- 执行动态SQL
EXEC sp_executesql @sql;

优点：零手动维护，新增传感器表后无需修改代码；用QUOTENAME避免SQL注入风险。
注意事项：确保所有传感器表的结构完全一致（列名、数据类型、顺序），否则UNION ALL会报错。如果有个别表结构差异，需要用别名统一列名，缺失列用NULL填充。

方案2：合并为单表（长远最优解） #

如果所有传感器表的结构完全相同，最根本的解决方案是把这些分散的表合并成一个带SensorID列的单表。比如原来的Sensor_001、Sensor_002，合并成SensorData表，新增SensorID字段（比如VARCHAR(20)）来区分不同传感器的数据。

合并后查询就变得极其简单：

SELECT * FROM SensorData;
-- 如需过滤特定传感器：
SELECT * FROM SensorData WHERE SensorID = 'Sensor_001';

优点：查询性能最优（可以针对时间、SensorID等字段建立联合索引）；维护成本极低，新增传感器只需插入数据，无需建表；避免了UNION ALL带来的执行计划开销。
缺点：需要迁移现有数据，可能需要修改依赖这些表的应用代码。如果已经有大量历史代码依赖分散表，可以考虑逐步迁移（比如先写入新表，同时保留旧表同步，待应用改造完成后再删除旧表）。

方案3：创建动态维护的视图 #

如果暂时无法修改应用代码或合并表，可以创建一个视图，用动态SQL定期更新视图定义，让视图自动包含所有传感器表的数据。

示例代码（创建或更新视图）：

DECLARE @viewSql NVARCHAR(MAX);

SELECT @viewSql = CONCAT(
    'CREATE OR ALTER VIEW vw_AllSensorData AS ',
    STRING_AGG(
        CONCAT('SELECT SensorID = ''', name, ''', * FROM ', QUOTENAME(name)),
        ' UNION ALL '
    )
)
FROM sys.tables
WHERE name LIKE 'Sensor_%';

EXEC sp_executesql @viewSql;

之后查询时直接用视图：

SELECT * FROM vw_AllSensorData;

优点：应用层无需修改，直接使用原有查询逻辑替换表名为视图即可；新增传感器表后，重新执行上述脚本更新视图即可。
缺点：视图本质上还是执行UNION ALL逻辑，性能和手动写的UNION ALL差不多，没有解决根本的性能问题，只是简化了维护。

额外注意点 #

• 无论用哪种方案，确保所有传感器表结构一致是关键，否则需要额外处理列映射问题。
• 如果传感器表数据量极大，合并单表后可以考虑按SensorID或时间字段做分区表，进一步优化查询性能。

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