Estimating Row Counts in SQL Server Without COUNT(*)
Working with large datasets in SQL Server often brings a simple question: “How many rows are in this table?”
The intuitive solution — running SELECT COUNT(*) FROM my_table — might work fine on small tables. But on large tables with millions (or billions) of records, this query can become painfully slow and resource-intensive, especially if you’re querying a production environment.
Luckily, there’s a much more efficient alternative: sp_spaceused.
🔍 Why COUNT(*) Can Hurt Performance
Let’s quickly understand why COUNT(*) is costly:
- SQL Server performs a full table scan, especially if there’s no optimized index to support the count.
- It can block or slow down other operations, especially under high concurrency.
- It’s not instantaneous — it needs to read every row.
This is where approximate counting becomes essential.
⚡ The Power of sp_spaceused
The built-in stored procedure sp_spaceused gives you a fast, metadata-based estimate of row count — without scanning the entire table.
```sql EXEC sp_spaceused ‘pcmi.ClaimDetails’;
This command is for MSSQL but there is equilavent types for other database Technologies too.