CODEWITHSUNDEEP
sql-serversql-execution-plan
Travis
Travis

Reputation: 21

Trying to understand sql execution plan

I created a simple table in SQL Server:

MemberId INT PRIMARY KEY Identity
Name NVARCHAR(100) NULL
Description NVARCHAR(250) NULL

The only index that was added was a clustered index when I created MemberId as a primary key. I didn't add an index to Name or Description.

I added about 30,000 rows of test data to the table and did the following query:

SELECT * FROM Members WHERE Name = 'Foo'

The execution plan said the following:

Clustered Index Scan - cost 100%

How is this a clustered index scan? I'm not predicating on the clustered index. I thought this would be more of a table scan. Can someone explain this to me? What exactly would cause a table scan if this does not?

Upvotes: 2

Views: 1559

Answers (2)

Iman
Iman

Reputation: 18906

if you change your primary key declaration with this

MemberId INT PRIMARY KEY NONCLUSTERED IDENTITY

you will find table scan until there is no clustered index . after creating a clustered index, your table structure will change and is ordered physically by your clustered index key(s). i think the table scan does not mean after that and is substituted with clustered index scan. whenever no useful index is found to cover the query

in another word table scan have meaning only when table data is not clustered and are stored as heap structure

What's the difference between a Table Scan and a Clustered Index Scan?

Upvotes: 1

a1ex07
a1ex07

Reputation: 37354

If the table has clustered index, clustered index scan is the same as full table scan. Clustered index by itself contains all data.

Upvotes: 5

Related Questions