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?

Chemist

Answer 1

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

Botanist

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

Tyndale

using case...when and/or if can be a bit slower than not using those. – Dispersoid

Answer 3

Like Other said you can use something like that:

IF NOT EXISTS (SELECT 1 FROM Table)
  BEGIN 
    --Do Something
  END 
ELSE
  BEGIN
    --Do Another Thing
  END

Jacinda

Answer 4

FOR the best performance, use specific column name instead of * - for example:

SELECT TOP 1 <columnName> 
FROM <tableName> 

This is optimal because, instead of returning the whole list of columns, it is returning just one. That can save some time.

Also, returning just first row if there are any values, makes it even faster. Actually you got just one value as the result - if there are any rows, or no value if there is no rows.

If you use the table in distributed manner, which is most probably the case, than transporting just one value from the server to the client is much faster.

You also should choose wisely among all the columns to get data from a column which can take as less resource as possible.

Poulson

Answer 5

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.

Launder

Answer 6

Fast:

SELECT TOP (1) CASE 
        WHEN **NOT_NULL_COLUMN** IS NULL
            THEN 'empty table'
        ELSE 'not empty table'
        END AS info
FROM **TABLE_NAME**

Refresh

Answer 7

IF EXISTS(SELECT TOP 1 1 FROM [MyTable]) BEGIN ... END ELSE BEGIN ... END;

This only every returns 1 or 0 rows, and doesn't even bring back a column value.

Leaden