What's the cheapest query I can run to see if there are any rows in the table?

Question

I've been using the sp_MSforeachtable built-in stored procedure to determine the row count of each table in our database, using COUNT(*).

I've realized, though, that I just want a 0 or 1, depending on whether there are any rows at all in the table.

Is there something else I can use that's faster/cheaper than COUNT(*)?

Have a look at http://stackoverflow.com/questions/1597442/subquery-using-exists-1-or-exists — StuartLC, Aug 09 '10 at 16:28
Whatever answers you come up with should be benchmarked. COUNT(*) is probably well optimized depending on the DBMS, in addition it clearly expresses the intent of what you are trying to do. — Romain Hippeau, Aug 09 '10 at 16:39

score 8 · Accepted Answer · answered Aug 09 '10 at 16:22

8

Consider this query. EXISTS will stop execution when it finds the first match.

IF EXISTS (SELECT 1 FROM MyTable)
BEGIN
   print 'at least one!'
END
ELSE
BEGIN
   print 'no rows found in table'
END

answered Aug 09 '10 at 16:22

p.campbell

@Downvoter: explain why this doesn't solve the problem and/or help the OP? – p.campbell Aug 09 '10 at 16:27

score 3 · Answer 2 · answered Aug 09 '10 at 16:26

3

This will print all the table names that have at least 1 row

exec sp_MSforeachtable 'if  exists (select 1 from ?) print ''?'''

answered Aug 09 '10 at 16:26

SQLMenace

Dean J · Answer 3 · 2010-08-09T17:00:12.073

0

Maybe just grab the first row, and display a 1?

select top 1 1 from tablename

edited Aug 09 '10 at 17:00

answered Aug 09 '10 at 16:20

Dean J

@p.campbell: you could just ask for the correction, instead of spending your own rep on downvoting. :) Correction look right? – Dean J Aug 09 '10 at 17:00
@p.campbell; gave you an upvote anyways, hopefully that makes up for it. – Dean J Aug 09 '10 at 17:07
actually I didn't downvote your answer! I agree with not wasting rep! But other definitely are downvoting my own! – p.campbell Aug 09 '10 at 17:08

score 0 · Answer 4 · answered Aug 09 '10 at 16:22

0

SELECT TOP 1 ID FROM TABLE

Then you can do an EOF check when the recordset is returned.

answered Aug 09 '10 at 16:22

Tom Gullen

score 0 · Answer 5 · answered Aug 09 '10 at 16:25

0

sp_spaceused will probably be more efficient than COUNT(*).

Keep in mind that it does not update in real time so it may not be 100% accurate in all cases.

answered Aug 09 '10 at 16:25

Dan Herbert

5 Answers5