Clustered InDex Scan... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Clustered InDex Scan...

Author

Topic

ramdas
Posting Yak Master

181 Posts

Posted - 2009-06-23 : 17:19:07

Hi,
I have two tables:

TableA
( ID INT,
Name VARCHAR(30),
flag CHAR(1)
)

Table B
(ID INT,
Procedure VARCHAR(40)
)

Both the tables TableA and TableB have clustered Indexes on ID, TableA has a nonclustered on flag.

When i run a query joining TableA and TableB joining on ID, in the execution plan i see a clustered Index scan, i was expecting to see a clustered index seek.
Any reasons why this is happening.

Thank you

Ramdas Narayanan
SQL Server DBA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-23 : 17:28:24

Can you post the exec plan? (as a .sqlplan file)

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-06-23 : 18:03:07

Also post the query.

Are your statistics up to date? How many rows are being returned? How many rows are there in the table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

ramdas
Posting Yak Master

181 Posts

Posted - 2009-06-23 : 21:04:19

SELECT a.ID,a.Name,b.Product
FROM tbla a
INNER JOIN tblb b
ON a.ID=b.ID
There is a clustered index scan on tbla and seek on tblb.

Ramdas Narayanan
SQL Server DBA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-24 : 02:38:51

Well, yeah. There would be.

There's no filters on the tables hence one of them has to be read completely. That would be TableA. Then, for each row of TableA, SQL does a seek on TableB to find rows that match. There's no way SQL can seek on both because it doesn't have values to seek on. It has to read one of them to get the values to use in the seek on the other one.

--
Gail Shaw
SQL Server MVP

ramdas
Posting Yak Master

181 Posts

Posted - 2009-06-24 : 09:35:30

Hi,
Thanks for the reply.
In case i provide a filter on tbla then there would be a clustered index seek on tbla correct.
Thank you

Ramdas Narayanan
SQL Server DBA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-24 : 11:08:57

If the filter was on the clustered index's leading key column, yes. If it's on some other column then you'll either get index seek (if there's a nonclustered index that has that as the leading key column) or a clustered index scan if there are no indexes with that column as the leading column

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources