My Index Frequently Asked Questions discussion follows the counting video. I distinguish exact results, estimates and the expression being counted.
1. Should I use NOLOCK with COUNT(*)?
NOLOCK can miss or duplicate rows in a count. Choose isolation that meets correctness requirements. Catalog counts provide whole-table estimates. They don’t replace every exact filtered count.
2. Is COUNT(1) faster than COUNT(*)?
COUNT(*) and COUNT(1) both count rows because 1 is non-NULL. My tests did not establish a universal advantage from rewriting COUNT(*). Compare plans and measurements before changing syntax.
3. Why can SELECT COUNT(*) work without FROM?
An aggregate can count the implicit single row without a table. SELECT * has no source columns to expand. The linked puzzle solution explains that behavior.
DECLARE @T table (ID int);
INSERT @T VALUES (1),(NULL),(3);
SELECT COUNT(*) AS AllRows,COUNT(1) AS ConstantCount,COUNT(ID) AS NonNullIDs FROM @T;
SELECT COUNT(*) AS CountWithoutFrom;
COUNT(ID) instead excludes NULL values. A suitable narrow index can support counting. Predicates, available indexes and cost determine the actual access path. Both counting videos provide context.
Related reading
- COUNT(*) and Index – SQL in Sixty Seconds #175
- Fastest Way to Retrieve Rowcount for a Table – SQL in Sixty Seconds #096
- Solution – Puzzle – SELECT * vs SELECT COUNT(*)
- youtube
A counting expression is not a universal access-path guarantee, it is a request whose plan depends on the workload.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
