sam360 sam360 - 1 year ago 100
SQL Question

SQL: Limit the number of row can be queried in SQL Server

I would like to force limit the number of records being queried from the SQL Server in a way that I can report back the to user that they need to optimize their query.

Intending to use it a reporting tool where let's say the user is not supposed to extract more than 10,000 records.

SET ROWCOUNT 10000 [THROW EXCEPTION IF MORE IS SELECTED]
//user's query
SELECT ....


Is this possible to do?

Answer Source

I think you are asking if you can set something global that would cause an error if the result set contained more than a certain number of records.

I also think the answer to that is, "No."

However, depending on how you are constructing and executing the queries you could possible prepend SET ROWCOUNT 10001 -- note the 1 to prevent the runaway queries you want to avoid.

Then append something like @Felipe suggested:

If @@ROWCOUNT = 10001 RAISERROR ('Too many results. Please, optimize your query', 1, 1);

If you are running all queries through some sort of central processor that would be fairly easy.

EDIT:

This should demo the idea in SSMS:

SET ROWCOUNT 2
SELECT 1 UNION SELECT 2
If @@ROWCOUNT = 2 RAISERROR ('Too many results. Please, optimize your query', 1, 1)

If you are not getting an error then it is being masked by something in you code.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download