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!

Index "INDEX" on table "TABLE" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

When I try to execute a simple SELECT statement, It's really slow and I found that the table performed TABLE SCAN.

But, The table also have a Nonclustered Index on it.

So, I try to force query optimizer to use the Index.

Select * from Tb_Sample with (Index =NCI_Id)
Go

oh. I got an error...

Msg 315, Level 16, State 1, Line 1
Index "NCI_Id" on table "Tb_Sample" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

The reason behind are..
1. The Index (NCI_Id) may be DISABLED.
2. The Filegroup in OFFLINE(Where the actual Index resides).

1. The Index (NCI_Id) may be DISABLED(Scenario)

/*Disabling Index*/
Alter Index NCI_Id on Tb_Sample DISABLE
Go

/*Forcing Index*/
Select * from Tb_Sample with (Index =NCI_Id)
Go

It througs an error...
Msg 315, Level 16, State 1, Line 2
Index "NCI_Id" on table "Tb_Sample" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

2. The Filegroup in OFFLINE(Where the actual Index resides)(Scenario)

/*Creating Index on different filegroup: IndexFilegroup*/
Create NonClustered Index NCI_Id on Tb_Sample(Id) on [IndexFilegroup]
Go
- File(Indexes) belongs to [IndexFilegroup] filegroup

/*Setting OFFLINE a particular file, which belongs to [IndexFilegroup] filegroup*/
Alter Database DatabaseName Modify File (Name=[Indexes],OFFLINE)
Go

Caution: It makes all the objects in the filegroup inaccessible, Only use when you have valid backup, Proper understanding/guidance

/*Forcing Index*/
Select * from Tb_Sample with (Index =NCI_Id)
Go

Now also, It througs an error...
Msg 315, Level 16, State 1, Line 2
Index "NCI_Id" on table "Tb_Sample" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

So, Index can not be forced the query optimizer when the index in DISABLED or The filegroup is OFFLINE where the Index resides.

/*To Enable the DISABLED Index*/
Alter Index NCI_Id on Tb_Sample Rebuild
Go

/*To set ONLINE the Filegroup again*/
A file set to OFFLINE can only be set ONLINE by restoring the file from the backup..