Colin Douglas Colin Douglas - 2 months ago 13
SQL Question

How to efficiently check if a table is empty?

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

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.