1

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 run 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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anthony
  • 11
  • 1
  • I always do this: SELECT count('x') FROM SOMETABLE; it doesn't have to fetch any column. I wouldn't run count() twice either. – Eric K Yung Dec 13 '10 at 17:02
  • @Eric K. Yung: it's an urban myth that `COUNT(1)` is more efficient than `COUNT(*)` - SQL Server's query optimizer is smart enough to understand that this is just a COUNT operation - it won't "fetch" all data just because of the (*) in the expression... – marc_s Dec 13 '10 at 17:27
  • @Eric K. Yung, @marc_s: urban myth indeed :-) http://stackoverflow.com/questions/1221559/count-vs-count1/1221649#1221649 – gbn Dec 13 '10 at 17:30
  • You let end users query a large table directly? – gbn Dec 13 '10 at 17:31
  • It's supposed to limited to system administrators but even they sometimes SELECT * FROM HUGETABLE and melt their server. – Anthony Dec 13 '10 at 17:41

3 Answers3

4

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.

Community
  • 1
  • 1
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
3

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.

Philip Fourie
  • 111,587
  • 10
  • 63
  • 83
0

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
wizzardz
  • 5,664
  • 5
  • 44
  • 66