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