2

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?

Travis
  • 21
  • 2
  • If searching by name is going to be a common requirement, you should try creating an identical table with a non-clustered index on that column, fill it with the same 30,000 rows, and then run a query against each table, and compare those plans. You should see a non-clustered index seek and it should be much more efficient (for this query, at least). – Aaron Bertrand Sep 08 '11 at 17:41

2 Answers2

5

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

a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • So the performance of a clustered index scan is not good if your table has a LOT of rows? If I had 50 million rows and ran this query it would probably perform very poorly? How would I modify this table/query so that I would see a table scan out of curiosity? – Travis Sep 08 '11 at 16:59
  • Sure, it will scan all rows if you don't have an index on `name` column. – a1ex07 Sep 08 '11 at 17:01
  • I believe you need to drop pk constraint, then pk, then create pk as non-clustered. So you will have heap table and `clustered index scan` will turn to `table scan`. – a1ex07 Sep 08 '11 at 17:31
  • 1
    Table scan or Clustered index scan is microsoft terminology. If the table contains a clustered index, you get the clustered index scan. If it doesn't, it's a heap and you'll get a table scan. A nonclustered index on Name should give you a plan with a nonclustered seek and a key lookup on the clustered. – brian Sep 08 '11 at 17:40
1

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?

Community
  • 1
  • 1
Iman
  • 17,932
  • 6
  • 80
  • 90
  • Since when are heaps best practice? And a clustered index scan is a table scan anway. – gbn Sep 08 '11 at 18:46
  • 1
    it is not best practice.he is Trying to understand sql execution plan and is looking for missing table scan .I thought this would be helpful for him to find the table scan , – Iman Sep 08 '11 at 19:08