不执行查询的 SqlDependency
是否可以在 C# 中为 Sql Server 运行 SqlDependency 而不执行查询？
我有一些桌子可以变得相当大。在表中执行查询来判断行是否已更改可能需要很长时间。
Is it possible to run a SqlDependency in C# for Sql Server without executing a query?
I have some tables that can grow fairly large. Executing a query across a table to tell if a row has changed can take a long time.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不可以，SqlDependency 需要 SqlCommand 才能执行。
如果性能是一个问题，您可以考虑替代设计，例如向要监视的表添加触发器，并在每次行更改时写入提取表，然后让 SqlDependency 监视提取表。
如果原始表中更改的任何行都应触发依赖性，那么您的提取表可以像包含已更改行数计数器的单行一样简单。当该值发生变化时，就会触发依赖关系。
或者，提取表可以仅包含表中任何行更改的最后日期/时间的时间戳。
No, SqlDependency requires an SqlCommand to execute.
If performance is a problem, you could think about alternative designs, such as adding a trigger to the table that is to be monitored and writing into an extract table each time a row changes then have the SqlDependency monitor the extract table instead.
If any row that changes in the original table should trigger the dependency, then your extract table could be as simple as a single row that contains a counter of the number of rows that have changed. When this value changes, the dependency would be triggered.
Alternatively, the extract table could just contain a timestamp for the last date/time that any row in the table was changed.
接受的答案有效，但如果您无法（或不想）使用触发器，则还有另一种选择。
使用带有每次提取数据时都会更改的参数的存储过程。例如：
那么您可以确保每次只返回一行，只要查询在主键或索引字段上，这就会降低 SQL 负载。
The accepted answer works, but there is an alternative if you're in a position where you can't (or don't want to) use triggers.
Use a stored procedure with a parameter that changes each time you pull data. For example:
Then you can be sure to return only one row each time, which should keep the SQL load down as long as the query is on a primary key or indexed field.