Limit the number of rows returned on the server side (forced limit)

Question

So we have a piece of software which has a poorly written SQL statement which is causing every row from a table to be returned. There are several million rows in the table so this is causing serious memory issues and crashes on our clients machine. The vendor is in the process of creating a patch for the issue, however it is still a few weeks out. In the mean time we were attempting to figure out a method of limiting the number of results returned on the server side just as a temporary fix.

I have no real hope of there being a solution, I've looked around and don't really see any ways of doing this, however I'm hoping someone might have an idea.

Thank you in advance.

EDIT

I forgot an important piece of information, we have no access to the source code so we can not change this on the client side where the SQL statement is formed. There is no real server side component, the client just accesses the database directly. Any solution would basically require a procedure, trigger, or some sort of SQL-Server 2008 setting/command.

Answer 1

One possible solution could be to

• rename the offending table
• create an updatable view with the original tablename
• do a SELECT TOP x * FROM OffendingTable as your view definition

As such, the client isn't aware of the change when selecting the data.

---

Use the query governor

If you don't mind returning no data at all for the offending query, the query governor allows you to do so.