I have an application using SQLDependency. I want to display the most recent row inserted into a database table to the user when a new row is inserted.
This works as expected when my query is a simple select statement, but given that I want to display the most recently inserted row, I wrote my query as a SELECT TOP statement. This caused multiple exceptions. Upon review of this question I learned that TOP is not valid with SQLDependency so I will have to find some other solution.
This made me wonder two things:
A) What is the reason for SQLDependency not supporting the TOP expression?
B) The solution I came up with is ordering the results based on id and just displaying the last one. This works fine, but my table has very few rows currently. I'm using a dataset, so I anticipate it slowing down once more rows are inserted - which was why I wanted to limit the query to only the most recent row. Is there a better way to do this?
This article explains how Query Notifications technology leverages the indexed views technology and therefore it has the same restrictions. In order to create an efficient indexed view one has to be able to update the index only from the current update, w/o looking at any other row in the table. If the TOP would be allowed then the million dollar question is: if you delete a row that was in the TOP which row should take its place? Answering this would require to search in the table another row that should be now included in the TOP indexed view, instead of the deleted one. Therefore and indexed views containing TOP (or, for the matter, MAX or MIN which suffer from the very same problem) cannot be maintained efficiently and are not permitted.
You can detect that a row was inserted by querying COUNT_BIG(*). Once you're notified that the count has changed, querying for the newly inserted row is trivial. You will also be notified on DELETES (ie. false positives).