What is an efficient query to count table rows in SQL Server 2008?

Question

What is an efficient query to count table rows in SQL Server 2008

Answer 1

For an exact answer, a reasonable way to get the exact number of rows in a table in a normalized database is:

select count(*) from table;

In a table without any indexes, the database will perform a full table scan. If you specify a non-empty column, the database may use a (potentially much smaller) index to resolve your query.

, , . , ( API ..) * .

, , , , , (, )

* : SQL-, , , Oracle.

Answer 2

SELECT COUNT(*) FROM TableName

?

Answer 3

SELECT COUNT(*) FROM table_name

. .

WITH NOLOCK, , .

SELECT sum(rows)
FROM sys.partitions 
where object_id=object_id('foo') and index_id < 2

However, this also will not give you a consistent response to the transaction.