'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.

sqlsql-serversql-server-2008


Solution 1:[1]

You could try something like this:

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

Tested on SQLFiddle

Solution 2:[2]

I wanted to write about EXISTS, but Sebastian Meine got it faster. Although I would prefer using EXISTS, there is one more method.

SELECT rows 
FROM sys.partitions 
WHERE object_id = object_id('MyTableName') 
    AND partition_number = 1

Solution 3:[3]

Please, use top 1 when you checking for existence because without it you will have as many 'ones' as a number of records:

IF NOT EXISTS(select top 1 1 from Table)
BEGIN
  RAISERROR('Error',0,1);
END;

Solution 4:[4]

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

Solution 5:[5]

Your variation is fine. You just need an alias on the subquery:

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

Solution 6:[6]

IF EXISTS can be used for check if a table is empty.

IF EXISTS (select * from YourTable)
SELECT 'Table is not empty'
ELSE
SELECT 'Table is empty'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 mustaccio
Solution 2 Stoleg
Solution 3 Iurii Kryvenko
Solution 4 Abe Miessler
Solution 5 Gordon Linoff
Solution 6 yilmazhuseyin

Related Questions