Testing if a table is empty 1

[pauljt]

Is there an alternative way to testing if a table is
empty other than using select count(*) from ..........?

Basically I am looking for a potentially quicker test,
as select count(*) from.... might take a fair bit of
time if the table is quite heavily populated...

 

[grega]

I can't think of any.

Greg.

 

[grega]

Hang on ... what you may be able to do is something along the lines of

set rowcount 1
select * from table
if @@rowcount = 0
   table is empty
else
   table has at least one row
** don't forget to set rowcount 0 again

Greg.

 

[Massy]

Hi Paul,

How about this:

select if exists (select 1 from table) then 1 else 0 endif from sys.dummy

Tom.

 

[pauljt]

thanks for the input guys..

based on the latter 'if exists' concept
- I have seen in practice that this is faster.

this is good as this will be part of a process
which polls the database regularly.

 

[JenJohnson]

I know that Paul has already taken an answer, but I thought I'd chime in anyway. What about something like this:

declare @table varchar(100)
select @table = "xxxx"

if (select rowcnt(i.doampg)
      from sysobjects o, sysindexes i
     where i.id = o.id
       and o.name = @table) = 0
    print "%1! EMPTY", @table
else
    print "%1! NOT EMPTY", @table

JenJohnson

 

[nikhilparchure]

Just adding to the above question
------------------------------------

How different is
if Exists ( Select * from tableName )
from
if Exists ( Select 1 from tableName )
In terms to execution....
Any responses would be appreciated.

Regards
Parchure Nikhil

 

[inaranjo]

Hi all,

I know this is late, but the command sp_spaceused tableName could help you too.

Regards,