PrismitaLEARN · PRACTICE · GROW

What is the difference between COUNT(*), COUNT(1) and COUNT(column)?

SQL Server T-SQL Basic Fresher1–2 Yrs SQL Query 1 min read

Quick Answer

COUNT(*) and COUNT(1) count rows and are identical in performance (the optimiser treats them the same). COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-null values.

COUNT(*) counts every row, and COUNT(1) is exactly the same — the optimiser doesn't care, the “COUNT(1) is faster” thing is a myth. COUNT(column) is different: it only counts rows where that column isn't null, which people actually use on purpose — COUNT(*) for the total and COUNT(ApprovedDate) for how many are approved, in the same query. And COUNT(DISTINCT column) gives distinct non-null values.

  • COUNT(*) — total rows, ignores nulls entirely (it's not looking at a column).
  • COUNT(NullableCol) — often used deliberately: SELECT COUNT(*) AS Total, COUNT(ApprovedDate) AS Approved FROM Requests;.
  • COUNT(1) vs COUNT(*) — no difference; the “COUNT(1) is faster” claim is a myth.
  • All COUNTs return a big integer; use COUNT_BIG(*) if a table can exceed ~2.1 billion rows.
All SQL Server questions More on T-SQL