在SQL Server 2012 Express上执行表压缩算法的最佳方法

Question

让我开始说，我有有限的SQL经验，但我已经搜索了几天找到一个很好的解决方案，我的问题，我还没有找到它。如果一个明显的答案存在，我没有使用正确的关键词来找到它。

我有一个硬件数据收集系统，它在一个表中插入一个系统健康行，在很长一段时间内每10分钟1次。这些数据在硬件出现故障时用作诊断信息。该表包含多个设备的记录，这些记录由唯一的DeviceID列标识。我计划在该表中为每个设备保留最多100K条记录。我计划偶尔使用以下算法压缩表:

当特定DeviceID的行数超过100K时，选择该特定DeviceID的最老的50%行，并每隔1秒删除这些行，从选择的第2行开始。

该算法具有所需的特征，即最近的数据每隔10分钟保留一次，而较旧的数据在时间上逐渐扩散。而且第一条(最老的)记录永远不会被删除。

我可以很容易地在c#中实现这一点，在查询之后删除多个单行，以选择最老的50K行。我希望有人知道更好的方法。

如果有帮助的话，我可以重新组织数据，以便每个设备都有一个不同的表，但这会使在c#中单个虚拟化网格视图中显示数据以供审查变得复杂。

谢谢你的帮助

Answer 1

我想我找到了一个解决方案，我的问题在这里(删除每n行SQL)使用一个共同的表表达式。在下面的代码中，HealthID是我的DeviceHealth表中的索引。DeviceID = 1子句仅用于测试。实际代码将使用参数而不是1。

WITH cte AS (
    SELECT TOP 50 PERCENT t.*, ROW_NUMBER() OVER (ORDER BY t.HealthID)
        AS rank FROM [DeviceHealth] t WHERE DeviceID = 1)
DELETE cte WHERE rank%2 != 0

这似乎有效。是否有更好的方法或找到一个好的解决方案?