Nearly a decade ago, I wrote a post called “Bad habits : Counting rows the hard way.” In that post, I talked about how we can use SQL Server’s metadata to instantly retrieve the row count for a table. Typically, people do the following, which has to read the entire table or index:
DECLARE @c int = (SELECT COUNT(*) FROM dbo.TableName);
To largely avoid size-of-data constraints, we can instead use sys.partitions.
Note: We could use OBJECT_ID() instead of a join, but that function doesn’t observe isolation semantics, so can cause blocking – or be a victim. A potential compromise is