Does a SELECT COUNT(*) query have to do a full table scan?

Question

Does a query that gets the count of all rows in a table have to do a full table scan or does SQL Server maintain a count of rows somewhere?

SELECT COUNT(*) FROM TABLE_NAME;

The table TABLE_NAME has a primary key, and therefore a clustered index, and looks like so:

CREATE TABLE TABLE_NAME
(
  Id int PRIMARY KEY IDENTITY(1, 1),
  Name nvarchar(50) NOT NULL
);

I am using Microsoft SQL Server 2014.

no, it will look up every row in the table, msql server will not maintain a counter — Nakul Narayanan, Feb 28 '19 at 08:52
The server does maintain a row count *elsewhere*, in `sys.partitions`. The query you wrote won't use it though. The query optimizer could decide to use a table scan or scan an index — Panagiotis Kanavos, Feb 28 '19 at 08:53

score 7 · Accepted Answer · answered Feb 28 '19 at 09:03

The server will always read all records (if there's an index then it will scan the entire index) to count the rows. You can't escape this as long as you are doing SELECT COUNT(*) FROM Table.

If your table has a clustered index, you can change your query to an "under the hood" query to retrieve the count without actually fetching the records with:

SELECT OBJECT_NAME(i.id) [Table_Name], i.rowcnt [Row_Count]
FROM sys.sysindexes i WITH (NOLOCK)
WHERE i.indid in (0,1)
ORDER BY i.rowcnt desc

if you are looking for an approximate count of the records, you can also use the following query:

SELECT 
    TableName = t.NAME,
    SchemaName = s.Name,
    [RowCount] = p.rows,
    TotalSpaceMB = CONVERT(DECIMAL(18,2), SUM(a.total_pages) * 8 / 1024.0), 
    UsedSpaceMB = CONVERT(DECIMAL(18,2), SUM(a.used_pages) * 8 / 1024.0),
    UnusedSpaceMB = CONVERT(DECIMAL(18,2), (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.0)
FROM 
    sys.tables t
    INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, 
    s.Name, 
    p.Rows
ORDER BY 
    TotalSpaceMB DESC

This will show non-system tables with their calculated (not exact) row count and the sum of the sizes of their data (with any index they might have), relatively fast without retrieving the records.

score 6 · Answer 2 · edited Feb 28 '19 at 09:00

6

When SQL Server performs a query like SELECT COUNT(*), SQL Server will use the narrowest non-clustered index to count the rows. If the table does not have any non-clustered index, it will have to scan the table.

If your table has a clustered index you can get your count even faster.

edited Feb 28 '19 at 09:00

Dale K

25,246
15
42
71

answered Feb 28 '19 at 08:53

Mano

780
3
15

So, if the table has a primary key and therefore a clustered index, does SQL Server do a full table scan or not? How does it then get the row count? – Water Cooler v2 Feb 28 '19 at 08:56
If table have primary key then it will not do the full table scan. For your reference http://dbatipster.blogspot.com/2009/08/get-row-counts-fast.html – Mano Feb 28 '19 at 09:09
1

@WaterCoolerv2 No - do not assume the primary key and the clustered index are always the same. – SMor Feb 28 '19 at 13:53

score 2 · Answer 3 · edited Feb 28 '19 at 09:00

2

SELECT COUNT(*) FROM TABLE_NAME;

Does a full table scan.

For optimizations you can refer to this.

edited Feb 28 '19 at 09:00

Dale K

25,246
15
42
71

answered Feb 28 '19 at 08:56

Abhicoding

115
1
7

score 0 · Answer 4 · answered Feb 28 '19 at 09:01

0

you can following way. it is better in performance I guess.

SELECT COUNT(1) FROM TABLE_NAME

answered Feb 28 '19 at 09:01

Ketan Kotak

942
10
18

1

Myth - you will get the same exact query plan. – SMor Feb 28 '19 at 13:53
I could see the difference in query output time if I go for count(*) and count(1) – Ketan Kotak Mar 01 '19 at 04:22

Does a SELECT COUNT(*) query have to do a full table scan?

4 Answers4

Linked