SELECT COUNT(*) - Always scans all the pages/rows ?

SELECT COUNT(*) always scans every rows (all pages/rows) in the table ?

No! - Not exactly! First, Ask the question - "The table has any Index ?"

How many Index are there in that table ? Each column type/size (allocation) ?

Why should I ask these questions ?

Here is the scenario!

A table "SystemObjects" created along with 2062 records: 
SELECT [schema_id], CAST([object_id] AS BIGINT) [object_id] , CAST(name AS NVARCHAR(200)) [name] INTO SystemObjects 
FROM sys.system_objects

Table structure:

Trying to get record count (with Actual Execution Plan)

SELECT COUNT(1) FROM SystemObjects

YES! - Now, we got Table Scan operation with 94%. Yeah - This is what always happen right ?
Partially Yes and No!

Lets create a Non-Clustered Index:
CREATE NONCLUSTERED INDEX nci_obj_id ON SystemObjects([Object_id])

- Non-clustered Index has been created on Object_id column which is 8 Byte!

Trying to get record count (with Actual Execution Plan)

SELECT COUNT(1) FROM SystemObjects









We got Index Scan operation based on object_id column

Lets create an another Non-Clustered Index:

CREATE NONCLUSTERED INDEX nci_schema_id ON SystemObjects([schema_id])

- Non-clustered Index has been created on schema_id column which is 4 Byte!

Trying to get record count again (with Actual Execution Plan)

SELECT COUNT(1) FROM SystemObjects

We got Index Scan operation again. But, this time based on schema_id column!!!

Confusing a bit right ?

See - How the Optimizer decides and goes based on what condition ? You believe Optimizer always tries to go with low cost. right ?

here is how...

Lets explore the current internal structure:
SELECT a.name, b.index_id, b.page_count, b.index_type_desc FROM sys.indexes a cross apply sys.dm_db_index_physical_stats(DB_ID(), a.[object_id] ,a.index_id,NULL,'LIMITED') AS b
WHERE a.[object_id] = OBJECT_ID('SystemObjects')

As you can see, The optimizer chooses to get the info from the smallest structure which will cost very less!!! right ? (Performance perspective)

here - The Optimizer decides to go with lowest pages to process with - So that the whole operation completes quickly/costs very less. Which is very effective/optimized operations.

The optimizer goes with "nci_schema_id" Index which is least pages (5 Pages)

If you remove the Index "nci_schema_id", The optimizer goes with "nci_obj_id" index which is least pages (6 Pages)

Once remove all the Indexes from the table - There is no other go - The Optimizer goes with Table scan (Scans all the Pages - 22 pages)

Here is the synopsis: The Optimizer goes with least pages which can be complete the process as quickly as possible with less cost!

Merry-go-round scanning ?

In SQL Server Enterprise edition, There is a strategy being followed when performing SCAN on tables! 

Its also called as "Advanced Scanning"

The advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. 

The Database Engine reads each page one time and passes the rows from each page to both execution plans. 

This continues until the end of the table's data is reached.

At that point, the first execution plan has the complete results of a scan, but the second execution plan must still retrieve the data pages that were read before it joined the in-progress scan. The scan for the second execution plan then wraps back to the first data page of the table and scans forward to where it joined the first scan. Any number of scans can be combined like this. The Database Engine will keep looping through the data pages until it has completed all the scans. This mechanism is also called "merry-go-round scanning" and demonstrates why the order of the results returned from a SELECT statement cannot be guaranteed without an ORDER BY clause.

i.e:, assume that you have a table with 500000 pages. User-1 executes a Transact-SQL statement that requires a scan of the table. When that scan has processed 100000 pages, User-2 executes another Transact-SQL statement that scans the same table. The Database Engine schedules one set of read requests for pages after 100001, and passes the rows from each page back to both scans. When the scan reaches the 200000 th page, User-3 executes another Transact-SQL statement that scans the same table. Starting with page 200,001, the Database Engine passes the rows from each page it reads back to all three scans. After it reads the 500000 th row, the scan for User-1 is complete, and the scans for User-2 and User-3 wrap back and start to read the pages starting with page 1. When the Database Engine gets to page 100000, the scan for User-2 is completed. The scan for User-3 then keeps going alone until it reads page 200000. At this point, all the scans have been completed

Without this strategy:
"Each user would have to compete for buffer space and cause disk arm contention. The same pages would then be read once for each user, instead of read one time and shared by multiple users, slowing down performance and taxing resources"