检查SQL Server表的更改？

Question

我该如何在不使用触发器或以任何方式修改数据库结构的情况下监视SQL Server数据库表的更改？我的首选编程环境是.NET和C#。

我希望能够支持任何SQL Server 2000 SP4或更新版本。我的应用程序是另一家公司产品的附加数据可视化工具。我们的客户基础有数千人，因此我不想在每次安装时都需要修改第三方供应商的表。

通过“对表的更改”，我指的是对表数据的更改，而不是对表结构的更改。

最终，我希望更改会触发我的应用程序中的事件，而不必定期检查更改。

---

根据我的要求（无触发器或模式修改，SQL Server 2000和2005），最好的做法似乎是在T-SQL中使用BINARY_CHECKSUM函数。我计划实现的方式如下：

每隔X秒运行以下查询：

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM sample_table
WITH (NOLOCK);

将其与存储的值进行比较。如果值发生了变化，请使用以下查询逐行遍历表格：

SELECT row_id, BINARY_CHECKSUM(*)
FROM sample_table
WITH (NOLOCK);

并将返回的校验和与存储的值进行比较。

- TimM

---

他们没有在行上放置最后修改的时间戳，是吗？ - zmbq

记录一下，如果版本支持SQL Server 2005或更高版本，我会查看SQL Server的Service Broker功能。 - Marco Guignard

Answer 1

看一下 CHECKSUM 命令：

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM sample_table WITH (NOLOCK);

只要表格内容没有更改，每次运行都会返回相同的数字。欲了解更多信息，请参考我的帖子：

CHECKSUM

以下是我如何在表格更改时使用它重新构建缓存依赖关系的方法：
ASP.NET 1.1数据库缓存依赖项（无触发器）

- Jon Galloway

---

校验和可能会最终失败。如果您的系统接受两个不同的数据集将导致相同的校验和，那么您就没问题了。因此，在我们的大多数系统中，我不得不放弃使用校验和... - LPains

@LPains，您能否详细说明一下您的陈述？ - petrosmm

@petrosmm 我不确定你具体想让我详细说明什么，但我会尝试。想象一下你有一个包含几百条记录的表，你基本上会生成一个整数作为校验和，那么它会有多少次碰撞？在我的情况下，我使用了大约10个表来做这件事，每天至少有一次碰撞。请查看这个答案 https://stackoverflow.com/questions/14450415/why-do-these-datetime-values-return-the-same-checksum-checksum-agg-how-can-i - LPains

Answer 2

遗憾的是CHECKSUM不总是能正确检测变化

它只是一个简单的校验和，没有循环冗余校验（CRC）计算。

因此，你不能使用它来检测所有变化，例如对称变化会导致相同的CHECKSUM！

例如，使用CHECKSUM_AGG(BINARY_CHECKSUM(*))解决方案将始终为具有不同内容的3个表提供0：

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM 
(
  SELECT 1 as numA, 1 as numB
  UNION ALL
  SELECT 1 as numA, 1 as numB
)  q
-- 提供0！


SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM 
(
  SELECT 1 as numA, 2 as numB
  UNION ALL
  SELECT 1 as numA, 2 as numB
)  q
-- 提供0！

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM 
(
  SELECT 0 as numA, 0 as numB
  UNION ALL
  SELECT 0 as numA, 0 as numB
)  q
-- 提供0！

- BitLauncher

---

这实际上不是一个答案，而是在说“你的建议行不通”。 - kristianp

通过在BINARY_CHECKSUM之前使用DISTINCT关键字，可以解决重复数据的问题。 还有一些其他讨论的陷阱可以在此处找到（http://stackoverflow.com/questions/11994430/what-conditions-cause-checksum-agg-to-return-0），但不是很常见的情况。 - pblack

Answer 3

为什么您不想使用触发器？ 如果使用正确，它们是一件好事。 如果将其用作强制参照完整性的方式，那么它们从好变成坏。 但是，如果将它们用于监视，它们不会被认为是禁忌。

- Nick Berardi

Answer 4

您需要多久检查一次更改，数据库中的表有多大（以行大小为单位）？ 如果您使用John建议的CHECKSUM_AGG（BINARY_CHECKSUM（*））方法，则会扫描指定表的每一行。 NOLOCK提示可以帮助缓解问题，但在大型数据库上，您仍然会遍历每一行。 您还需要存储每一行的校验和，以便告诉哪些已更改。

您考虑过从不同角度入手吗？ 如果您不想修改模式以添加触发器（这是有道理的，因为这不是您的数据库），您是否考虑与制作数据库的应用程序供应商合作？

他们可以实现提供通知辅助应用程序数据已更改的机制的API。 它可以很简单，只需编写到列出已修改的表和哪个行的通知表即可。 可以通过触发器或应用程序代码实现该功能。 就您而言，扫描通知表的周期性是唯一的关注点。 数据库的性能影响将远小于扫描每行以查找更改的影响。

难点在于说服应用程序供应商实施此功能。 由于这可以完全通过SQL通过触发器处理，因此您可以编写并测试触发器的大部分工作，然后将代码带给应用程序供应商。 通过让供应商支持触发器，可以防止您意外添加触发器代替供应商提供的触发器。

- Chris Miller

Answer 5

很遗憾，我认为在SQL2000中没有干净利落的方法来做到这一点。如果你将要求缩小到SQL Server 2005（及更高版本），那么你就可以使用System.Data.SqlClient中的SQLDependency类。请参见ADO.NET中的SQL Server查询通知.

- caryden

Answer 6

有一个DTS作业（或由Windows服务启动的作业），它按照给定的时间间隔运行。每次运行时，它使用系统INFORMATION_SCHEMA表获取有关给定表的信息，并将此数据记录在数据存储库中。将返回的关于表结构的数据与上一次返回的数据进行比较。如果不同，则知道结构已更改。

示例查询，以返回有关表ABC中所有列的信息（最好只列出想要的INFORMATION_SCHEMA表中的列，而不是像我这样使用*select**）：

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'ABC'

根据您对“表更改”的定义，您需要监视不同的列和 INFORMATION_SCHEMA 视图。

- Yaakov Ellis

---

问题涉及表数据的更改，information_schema 包含表的模式（列定义）。 - too

Answer 7

这里有一个猜测：如果您不想修改第三方的表，您是否可以创建一个视图，并在该视图上放置触发器？

- Orion Edwards

Answer 8

检查最后一次提交的日期。每个数据库都有记录每次提交的历史记录，我相信这是符合ACID标准的。

- ECE

---

请提供一种记录的方法，用于在SQL Server中将此信息作为表进行范围限定。 - Martin Smith

Answer 9

SQL Server有内部跟踪更改的视图。它们包含了更改发生的时间、查找和查看发生的时间的列，详见sys-dm-db-index-usage-stats。

SELECT OBJECT_NAME(OBJECT_ID) AS TableName, *
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'Your DB Name')
ORDER BY last_user_update DESC

注意事项：

• 每次 SQL Server 重新启动时，此信息都会被重置
• 您需要 VIEW SERVER STATE 来查看 sys.dm_db_index_usage_stats
• 这适用于任何具有索引的内容（大多数表至少有一个聚集索引）。不确定堆是否适用，我还没有测试过。

- Dave Glassborow