Anything faster than count(1) from tableA to find row counts ?

Question

Is there any thing from which we can faster get the no. of rows in a table instead of using count(1). Further there are two cases :

a) when we want to get the no. of rows in a table.

b) when we just want to know if there is at-least one row.

Thanks in advance.

Answer 1

b) when we just want tot know if there is atleast one row.

SELECT TOP 1 myField FROM myTable [WHERE .....]

Answer 2

There is this approach, using sysobjects and sysindexes:

http://stackoverflow.com/questions/428458/counting-rows-for-all-tables-at-once

It's worked well for me in the past.

Answer 3

b) Linq-to-Sql generates

EXISTS (SELECT NULL FROM Table....)

Answer 4

a) You can get table row counts from system tables, 1 example is as per Uri Dimant's blog post here.

b) I'd always use EXISTS:

IF EXISTS(SELECT * FROM YourTable)

Answer 5

a) No.

b) You can use the exists function, which only checks if there is a result and doesn't actually get the result.

Answer 6

To know the count of all rows in the table without scanning them you can use

select SUM(row_count) AS row_count
from sys.dm_db_partition_stats
where object_id = object_id('dbo.tblName')
    and index_id < 2

I'm not sure if there are any caveats with that approach. (Presumably it might either include the count for non committed records or not include changes made within your current transaction)