Check whether a table contains rows or not sql server 2005

Question

How to Check whether a table contains rows or not sql server 2005?

Answer 1

select count(*) from table;

Will return 0 if it is empty.

Answer 2

Also, you can use exists

select case when exists (select 1 from table) 
          then 'contains rows' 
          else 'doesnt contain rows' 
       end

or to check if there are child rows for a particular record :

select * from Table t1
where exists(
select 1 from ChildTable t2
where t1.id = t2.parentid)

or in a procedure

if exists(select 1 from table)
begin
 -- do stuff
end

Answer 3

Can't you just count the rows using select count(*) from table (or an indexed column instead of * if speed is important)?

If not then maybe this article can point you in the right direction.

Answer 4

For what purpose?

• Quickest for an IF would be IF EXISTS (SELECT * FROM Table)...
• For a result set, SELECT TOP 1 1 FROM Table returns either zero or one rows
• For exactly one row with a count (0 or non-zero), SELECT COUNT(*) FROM Table