Will SQL Server always Perform Table Scan to find the result of SELECT COUNT (*) FROM mytable

The answer is NO. The Query processor will use the index with smaller number of pages. Ie., with the least I/O cost.

Lets verify this with the below example:

Create the below table with all default values and loop it for 10000 records with out any indexes

Create table mytable (Col1 int Identity, Col2 Bigint Default 1, Col3 Char(100) Default 'SQL')

GO

SET NOCOUNT ON

GO

Insert into mytable Default values;

GO 10000

Execute the below command

select COUNT(*) from mytable

The query processor has no choice it will do a table scan because no index is attached to the table.

Now we will create an index on Col2 as below and execute select count(*) command

create nonclustered index Col2_Index on mytable(Col2)

select COUNT(*) from mytable

If you observe the below output table scan will become index scan and cost comes down from 96% to 85%

Now we will create an index on Col1 which is lower in size in terms of storage because it has int column and we will execute the select count(*) command

create nonclustered index Col1_Index on mytable(Col1)

select COUNT(*) from mytable

If you observe the below output the cost again come down from 85 to 82%.

This is how select count(*) will works. Lets see the page count of each index

select INDEX_ID, page_count,id.name

from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('mytable'),NULL,NULL,'LIMITED') dmd, sysindexes id

where dmd.index_id = id.indid and object_id('mytable') =id.id