SQL Server: Why Clustered Index Scan and not Table Scan?

Question

I have following table schema -

CREATE TABLE [dbo].[TEST_TABLE]
(
    [TEST_TABLE_ID] [int] IDENTITY(1,1) NOT NULL,
    [NAME] [varchar](40) NULL,
    CONSTRAINT [PK_TEST_TABLE] PRIMARY KEY CLUSTERED 
    (
        [TEST_TABLE_ID] ASC
    )
)

I have inserted huge data in TEST_TABLE.

As I have marked TEST_TABLE_ID column as primary key, clustered index will be created on TEST_TABLE_ID.

When I am running following query, execution plan is showing Clustered Index Scan which is expected.

SELECT * FROM TEST_TABLE WHERE TEST_TABLE_ID = 34

But, when I am running following query I was expecting Table Scan as NAME column does not have any index:

SELECT * FROM TEST_TABLE WHERE NAME LIKE 'a%'

But in execution plan it is showing Clustered Index Scan.

As NAME column does not have any index why it is accessing the clustered index?

I believe, this is happening as clustered index resides on data pages.

Can anyone tell me if my assumption is correct? Or is there any other reason?

Answer 1

A clustered index is the index that stores all the table data. So a table scan is the same as a clustered index scan.

In a table without a clustered index (a "heap"), a table scan requires crawling through all data pages. That is what the query optimizer calls a "table scan".

Answer 2

As others explained already, for a table that has a clustered index, a Clustered Index Scan means a Table Scan.

In other words, the table is the clustered index.

What you have wrong is your first query execution plan:

SELECT * 
FROM TEST_TABLE 
WHERE TEST_TABLE_ID = 34 ;

It does a Clustered Index Seek and not a Scan. It doesn't have to search (scan) the whole table (clustered index), it goes directly to the point (seeks) and checks if a row with id=34 exists.

You can see a simple test in SQL-Fiddle, and how the two execution plans differ.