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

Question

I used the built-in stored procedure sp_MSforeachtableto determine the number of rows of each table in our database using COUNT (*).

I realized that I just want 0 or 1, depending on whether there are any rows in the table.

Is there anything else I can use faster or cheaper than COUNT (*)?

Answer 1

Consider this request. EXISTSwill 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

Answer 2

This will print all table names that contain at least 1 row

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

Answer 3

, 1?

select top 1 1 from tablename

Answer 4

SELECT TOP 1 ID FROM TABLE

You can then perform an EOF check when returning a recordset.

Answer 5

sp_spaceusedwill probably be more effective than COUNT(*).

Keep in mind that it is not updated in real time, so it cannot be 100% accurate in all cases.