CODEWITHSUNDEEP
sql-serverdatabasesql-server-2014
Water Cooler v2
Water Cooler v2

Reputation: 33880

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

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.

Upvotes: 4

Views: 13505

Answers (4)

EzLo
EzLo

Reputation: 14199

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.

Upvotes: 8

Ketan Kotak
Ketan Kotak

Reputation: 957

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

SELECT COUNT(1) FROM TABLE_NAME

Upvotes: 0

Mano
Mano

Reputation: 788

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.

Upvotes: 6

Abhicoding
Abhicoding

Reputation: 115

SELECT COUNT(*) FROM TABLE_NAME;

Does a full table scan.

For optimizations you can refer to this.

Upvotes: 2

Related Questions