I found this good article SQL Server–HOW-TO: quickly retrieve accurate row count for table from martijnh1 which gives a good recap for each scenarios.

I need this to be expanded where I need to provide a count based on a specific condition and when I figure this part, I'll update this answer further.

In the meantime, here are the details from article:

Method 1:

Query:

SELECT COUNT(*) FROM Transactions

Comments:

Performs a full table scan. Slow on large tables.

Method 2:

Query:

SELECT CONVERT(bigint, rows) 
FROM sysindexes 
WHERE id = OBJECT_ID('Transactions') 
AND indid < 2

Comments:

Fast way to retrieve row count. Depends on statistics and is inaccurate.

Run DBCC UPDATEUSAGE(Database) WITH COUNT_ROWS, which can take significant time for large tables.

Method 3:

Query:

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'Transactions' 
AND SCHEMA_NAME(tbl.schema_id)='dbo'))

Comments:

The way the SQL management studio counts rows (look at table properties, storage, row count). Very fast, but still an approximate number of rows.

Method 4:

Query:

SELECT SUM (row_count) 
FROM sys.dm_db_partition_stats 
WHERE object_id=OBJECT_ID('Transactions')    
AND (index_id=0 or index_id=1);

Comments:

Quick (although not as fast as method 2) operation and equally important, reliable.



Index

Query

Comment

1

SELECT COUNT(*) FROM Transactions

Performs a full table scan. Slow on large tables.

2

SELECT CONVERT(bigint, rows)

FROM sysindexes

WHERE id = OBJECT_ID('Transactions')

AND indid < 2

Fast way to retrieve row count. Depends on statistics and is inaccurate.

Run DBCC UPDATEUSAGE(Database) WITH COUNT_ROWS, which can take significant time for large tables.

3

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'Transactions'

AND SCHEMA_NAME(tbl.schema_id)='dbo'))

The way the SQL management studio counts rows (look at table properties, storage, row count). Very fast, but still an approximate number of rows.

4

SELECT SUM (row_count)

FROM sys.dm_db_partition_stats

WHERE object_id=OBJECT_ID('Transactions')   

AND (index_id=0 or index_id=1);

Quick (although not as fast as method 2) operation and equally important, reliable.

Ref:

https://docs.microsoft.com/en-us/archive/blogs/martijnh/sql-serverhow-to-quickly-retrieve-accurate-row-count-for-table

https://www.brentozar.com/archive/2014/02/count-number-rows-table-sql-server/