Take the 2-minute tour ×

I was wondering if there was a constraint or way to limit the total number of rows a table returns if there is no top clause in the query executed. We can't easily modify the SQL being executed so the solution needs to be on the db level.

We have a lot of queries our system blindly builds that are like

select * from table where email = 'john'

and when we had small tables this worked fine but now our tables have +10000 rows and this is becoming inefficient.

Thanks for any suggestions.

share|improve this question

closed as unclear what you're asking by benni_mac_b, bobs, Szymon, Frédéric Hamidi, Maverick Mar 4 at 10:14


First of all: a TOP without ORDER BY is pointless - you want to top 5 rows - but ordered by what?!?! You need to always define an ORDER BY. Once you do - you can use an index to speed up this ORDER BY clause (but not if you use SELECT * .... stop doing that - select exactly those columns that you really need) –  marc_s Jun 21 '12 at 15:47
If code broken is, code fix you should. In other words, no amount of server tinkering can fix bad code. –  Panagiotis Kanavos Jun 21 '12 at 15:48
Hmm, a server setting that means a SELECT without a WHERE clause doesn't return all rows? I can't ever see that causing problems :) –  AakashM Jun 21 '12 at 15:57

2 Answers 2

The answer is "yes". One way is using "set rowcount". Another way is to use any facility built into your query connection for this purpose.

For instance:


This sets the maximum rows returned to 10 for any subsequent queries. However, to fit this into your application, we need more information, such as how that application is accessing the database.

share|improve this answer

it's a bit convoluted:

y  char(9) PRIMARY KEY,
x  char(9)

insert into temp (x, y) values ('j', 'hello');
insert into temp (x, y) values ('j', 'world');
insert into temp (x, y) values ('q', 'foo');
insert into temp (x, y) values ('q', 'bar');

with myCTE(x,y,r) as
from temp
select * from myCTE where r <= 3;
share|improve this answer
