This execution plan is weird
-
CREATE TABLE [dbo].[a] (
[c1] [int] NOT NULL ,
[c2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[a] WITH NOCHECK ADD
CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
(
[c1]
 
WITH FILLFACTOR = 90 ON [PRIMARY]GO
SQL :select c2 from a where c2 like '%aaa%'
I think this SQL would be executed in "table scan" because:
1.There is no index on c2.
2.The information fetched back is just about c2 value.
3. There is a "like" in where clause
But the execution plan is still used "clustered index scan". why?
-
The wording is misleading. You are correct in your assumption about TABLE SCAN however, due to CLUSTERED INDEX the code is performing a TABLE SCAN on the INDEX. If you run profiler and add statistics and binary data you can review the # of rows retrieved and it should be close to the # of rows in the table.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Thanks, AJ.
Your meaning is Sql-Server do execute a TABLE SCAN, but on the index not on the real table, so Sql-Server still call this 'Clustered Index Scan'. But I don not think there is something about Column C2 on the index, So it is impossible to access index. You said 'it is misleading'. when I tune the SQL perfermance, this execute plan will lead me to ignore it and think it work well.
-
If you have a clustered index (primary key clustered as in your example) sql will always do a clustered index scan rather than a table scan for this sort of query.
If it helps you understand things better, the clustered index effectively is all of the data in the table (I know that's not strickly true but it's fine for explaining the concept
). -
Another item to think about is.. IF you are strictly looking at Column2 for the WHERE statement it SHOULD be indexed. If you add an index to it you will probably see an INDEX SEEK....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
So, why do we continue to create an index on those non-primary-keys columns? Just a primary key would safeguard the better execution plan since whether a criteria is used on primary key column or not, query engine would be wisely enough to choose the clustered index to execute. System performance tuning is just setting a primary key on each table and let it go? It seems ridiculous. I think there must be something I should know.
-
System performance tuning is so much more than just setting primary keys and away you go. Other indexes are created because it's more appropriate to use them for certain queries.
If your table had 2 million rows, which do you think would be quicker, reading every row via the clustered index, or reading a small proportion where the data was covered by an appropriate index?
On top of that you also need to take into account the sometimes significant extra work that is required to insert data into multiple indexes. The more indexes the slower inserts, updates and deletes are. It's a very fine line trying to tune select performance without impacting on inserts and the like.
There are several good books on this topic, I'd recommend Kalen Delany's inside SQL server 2000.
-
Thanks,Mike.
Yes. I understand performance tuning does not deal with creating an index only. I just put the question to extreme. The execution plan contradicts my understanding of clustered index and nonclustered index.
On the leaf node of clustered index, there is a real value of indexed column. On the leaf node of nonclustered index, there is a RID. The table that has a clustered index organizes the data in the order of clustered column phisically. So when you look up the clustered column value in your SQL statement, query engine will return the value from clustered index directly without accessing the real table. It is the most efficient way to return data when index range scan is executed. Some articles recommend setting clustered index on those columns on which most execution plans are range scan, such as those queries containing “column1=2” or “column1>2 and column2<10” or “column1 between 2 and 10”.
I believe that in my case, the execution plan is confusing. What I want to know is the internal mechanism of clustered index. My resource on hand cannot explain it.
-
A clustered index scan is the same as a table scan. Note that this is different than index seek. An index seek is only performed on the column on which you have an index. The execution plan will show a table scan when you have a heap table(i.e. no clustered index). But when you define a clustered index on the table, the execution plan would always come up as an clustered index scan instead of a table scan.
Hope this helps.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply