Why can't TOP be used with SQLDependency?

Question

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?

Is it really your goal to show them the last row that was inserted (even if it was inserted by someone else)? Usually you do this with existing mechanisms, e.g. `SCOPE_IDENTITY()`. — Aaron Bertrand, Sep 12 '12 at 19:13
Yes, it is. All this application does is display some counts of other data. It uses a "temporary" table - another process updates the counts at certain intervals and inserts a new row into the "temporary" table containing the updated counts. Since the last row will always have the most up to date counts, that's why I want to display it. — Mansfield, Sep 12 '12 at 19:15
Why not also (or alternatively) update a single-row table with the counts? — Aaron Bertrand, Sep 12 '12 at 19:16
That was my original thought, but we decided we'd like to keep a history of what the counts were at certain times, hence adding new rows to a table rather than updating one row. — Mansfield, Sep 12 '12 at 19:17
Well you could do both to work around this problem. Though I am not sure why perfectly valid SQL is not supported here. — Aaron Bertrand, Sep 12 '12 at 19:18
Yes, I'm quite curious as to why it isn't supported as well. My interim solution (in my post) is fine for now, I'll just need to remember to purge old records from the table occasionally so things don't slow down. — Mansfield, Sep 12 '12 at 19:22
Seems like quite a cumbersome workaround compared to adding a trigger to that table to update a second, one-row table. — Aaron Bertrand, Sep 12 '12 at 19:26

score 5 · Accepted Answer · answered Sep 12 '12 at 19:54

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).

Excellent. Just what I was looking for! – Mansfield Sep 12 '12 at 20:39 — Mansfield, Sep 12 '12 at 20:39

score 0 · Answer 2 · answered Aug 04 '20 at 22:32

@Remus Rusanu explained well. You cannot use the TOP keyword for SQL Command. But, still if you want to select some TOP data, you can use the below alternative solution.

I am selecting all records from the database, But I am putting just ten records in my list and I am returning this list to my dashboard. So, every time I am receiving the latest ten records.

You can use your current SQL command without the TOP keyword. Mine Is:

SQL Command:

Select [Id], [Name] FROM dbo.CUSTOMER where InsertDate = @InsertDate ORDER BY [ID] DESC;

Then, In your application, you can fill your List based on your top count.
Check my comment on below source code.

public List<CustomerModel> GetAllCustomer()
        {
            List<CustomerModel> lstCustomerModel = new List<CustomerModel>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                string InsertDate = string.Empty;
                InsertDate = DateTime.Now.ToString("yyyyMMdd");

                string query = "SELECT [Id] " +
                                    ",[Name] " +
                                "FROM [dbo].[Customer] where InsertDate = " + InsertDate + "   ORDER BY [Id] DESC;";
                SqlCommand cmd = new SqlCommand(query, conn);
                cmd.CommandType = CommandType.Text;
                cmd.Notification = null;
                SqlDependency.Stop(connectionString);
                SqlDependency.Start(connectionString);
                SqlDependency sqlDependency = new SqlDependency(cmd);
                sqlDependency.OnChange += OnDependencyChange;

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        int counter = 0;
                        while (reader.Read())
                        {
                            if (counter == 10) /* Here, I am reading just first ten record. After 10 records, I am not filling my List. So, It is kind of top 10 records.. Alternative solution.*/
                                break;
                            counter++;
                            
                            lstCustomerModel.Add(new CustomerModel
                            {
                               Id = Convert.ToInt32(reader.GetValue("Id")),
                               Name = WeightUnit = reader.GetValue("Name")
                            });

                            //break;
                        }
                    }
                }
            }

            return lstCustomerModel;
        }

        private void OnDependencyChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {
                _context.Clients.All.SendAsync("refreshCustomers");
            }
        }

Why can't TOP be used with SQLDependency?

2 Answers2