SqlDependency Without Executing a Query

Question

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.

• MethodMan: there is a site that will help you answer that question msdn.microsoft.com/en-us/library/62xk7953(VS.80).aspx This site will show users how to subscribe to events when needing to check sqlDependency
• Stuart: I have a working SqlDependency set up. I was just wondering if there was a way to do it without executing the "command.ExecuteResult()" command. I have set it up to work with "command.ExecuteNonQuery()", which works fine, but it still slows down when the table has several million rows to re-execute.

Answer 1

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.

Answer 2

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:

Alter PROCEDURE someschema.SQLDependency_DC_PRE
 @MaxDCIdx INT = 1
AS
SELECT DCIdx FROM someschema.DC_PRE WHERE DCIdx >= @MaxDCIdx ORDER BY DCIdx DESC;

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.