(One of) the fastest? Yes. The most reliable? I don't think so, because that query counts both committed and uncommitted records, while the "SELECT COUNT(*)" statement counts only committed records.

Here is an example.

1) Create a table:

CREATE TABLE dbo.QOTD_TEST (ID INT);

2) Insert several records into the table, don't commit them:

BEGIN TRANSACTION;

INSERT dbo.QOTD_TEST VALUES(1);

INSERT dbo.QOTD_TEST VALUES(2);

INSERT dbo.QOTD_TEST VALUES(3);

3) In another query window (e.g. in another connection) run queries 2-4:

SELECT CONVERT(bigint, [rows])

FROM sys.sysindexes

WHERE id = OBJECT_ID('dbo.QOTD_TEST') AND indid < 2

SELECT CAST(p.[rows] AS float)

FROM sys.tables AS tbl

INNER JOIN sys.indexes AS idx ON idx.[object_id] = tbl.[object_id] And idx.index_id < 2

INNER JOIN sys.partitions AS p ON p.[object_id] = CAST(tbl.[object_id] AS int) And p.index_id=idx.index_id

WHERE ((tbl.name = N'QOTD_TEST' AND SCHEMA_NAME(tbl.schema_id) = 'dbo'));

SELECT SUM (row_count)

FROM sys.dm_db_partition_stats

WHERE object_id = OBJECT_ID('dbo.QOTD_TEST') AND (index_id=0 or index_id=1);

All these queries return the value 3.

4) After that, run the 1st query:

SELECT COUNT(*) FROM dbo.QOTD_TEST;

If the database READ_COMMITTED_SNAPSHOT option is set to ON, this query will immediately return the value 0. If READ_COMMITTED_SNAPSHOT = OFF, this query will wait until the locks are removed.

5) In 1st query window, roll back the transaction and drop the table:

ROLLBACK TRANSACTION;

DROP TABLE dbo.QOTD_TEST;

I think that the 1st query is the most reliable, while others are the fastest.