How to efficiently check if a table is empty?

Question

In the program I'm currently writing there is a point where I need to check whether a table is empty or not. I currently just have a basic SQL execution statement that is

Count(asterisk) from Table

I then have a fetch method to grab this one row, put the Count(asterisk) into a parameter so I can check against it (Error if count(*) < 1 because this would mean the table is empty). On average, the count(asterisk) will return about 11,000 rows. Would something like this be more efficient?

 select count(*) 
 from (select top 1 * 
        from TABLE)

but I can not get this to work in Microsoft SQL Server

This would return 1 or 0 and I would be able to check against this in my programming language when the statement is executed and I fetch the count parameter to see whether the TABLE is empty or not.

Any comments, ideas, or concerns are welcome.

Answer 1

You are looking for an indication if the table is empty. For that SQL has the EXISTS keyword. If you are doing this inside a stored procedure use this pattern:

IF(NOT EXISTS(SELECT 1 FROM dbo.MyTable))
BEGIN
  RAISERROR('MyError',16,10);
END;

IF you get the indicator back to act accordingly inside the app, use this pattern:

SELECT CASE WHEN EXISTS(SELECT 1 FROM dbo.MyTable) THEN 0 ELSE 1 END AS IsEmpty;

While most of the other responses will produce the desired result too, they seem to obscure the intent.

Answer 2

You could try something like this:

select count(1) where exists (select * from t)

Tested on SQLFiddle

Answer 3

This seems like an odd way to get what you want. Can you just use HAVING instead?

SELECT id, name FROM TABLE
GROUP BY id, name
HAVING COUNT(*) > 0