Generic SQL Server Count Syntax

In SQL Server 2008, I have an application where users can run queries against a database. I want to prevent them from running a query that will return millions of results and tax system resources. The current solution is wrap whatever query is input in a count(*) function like so:

Select count(*) as COUNT 
from (SELECT SOMETHING FROM SOMETABLE) as TMPCOUNT0;

Works fine until the user tries to r开发者_如何学编程un COUNT(*) on their own.

Select count(*) as COUNT 
from (SELECT COUNT(*) FROM SOMETABLE) as TMPCOUNT0;  

--(should return 1)

However SQL Server does not like that my inner COUNT(*) column has no name in my derived table and errors out with:

No column name was specified for column 1 of 'TMPCOUNT0'.

I know I could fix this if I were running the query by specifying a name for inner count, but since end users are not aware of this, I'm hoping to find a more elegant solution.

Any ideas?

Back before the Stack Exchange Data Explorer existed I played with a little site to let people run queries on the public Stack Overflow data set. My initial offering was powered by an Atom 330 with only 2GB of RAM hosted at my home, and so I was very concerned about limited queries to prevent them from both taking over my server completely and flooding my home broadband connection.

What I ended up with was a combination of the SET ROWCOUNT option mentioned in @Philip Fourie's answer and SET QUERY_GOVERNOR_COST_LIMIT. The latter takes a lot of tuning, and both can be overridden if you user knows or thinks to try it, so I also logged every query so I could spot abuse.

This combination has it weaknesses, but it worked pretty well.

Not sure how to solve you COUNT(*) issue but what about limiting the result by specifying

SELECT TOP(1000) ...

or alternatively

SET ROWCOUNT 1000

to return the first thousand rows.

The error

No column name was specified for column 1 of 'TMPCOUNT0'.

occurred because of the fact that you haven't specified the a column name in the inner query.

The inner query should read as

SELECT COUNT(*) **as c** FROM SOMETABLE as TMPCOUNT0

This query will return 1 , but you have to specify the alias name for that count in the inner query.

Select count(c) as [COUNT] from (SELECT COUNT(*) as c FROM SOMETABLE) as TMPCOUNT0