July 30, 2008 at 5:07 am
Hi,
Just trying to better unstand the index usage.
Scenario,
I have a table;
CREATE TABLE dbo.SampleTableA
(
ColumnA INT,
ColumnB VARCHAR(255)
)
then i create a clustered index on the table
CREATE CLUSTERED INDEX CIX_SampleTableIndex ON SampleTableA (ColumnA)
When i run a select statment;
SELECT * FROM SampleTableA WHERE ColumnA = 1
it uses the clustered index in the execution, however when i execute the second select statement
SELECT * FROM SampleTableA WHERE ColumnB = 'a'
it uses the same plan, and the again uses the clustered index scan.
I am a bit confused, as 'ColumnB' is not part of the clustered index how come it is using it in the plan for the second select?
Thanks,
Nic
July 30, 2008 at 6:33 am
Here is my take on it. (90% confident)
Because it is a clustered index, the leaf level pages of the index is the data rows in the table. So you will always see an index scan for the table even though it is doing a table scan.
SQL server will report an index scan on the table when it does a table scan because when it moves through the table scan it is actually scanning the index pages as well - they are the same.
July 30, 2008 at 6:40 am
A clustered index scan is a table scan. It's a full and complete read of all of the data pages in the table. You see the operation as a table scan if the table is a heap (has no clustered index) or as a clustered index scan if there is a clustered index.
It's using the clustered index because there is no index on ColumnB, hence no other way to find the rows other than to scan the entire table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2008 at 6:44 am
Thanks both,
That makes perfect sense.
Nic
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply