difference between index scan, index seek and table scan

Question

Post reply

difference between index scan, index seek and table scan

vamshi.kotagiri

SSC Enthusiast

Points: 142
More actions
November 15, 2010 at 7:44 pm

#226638

I just want to know the difference between index scan and index seek.
I have created two tables with id, title as the columns where id is of datatype integer and title is of datatype varchar(50)
The first table contains no indexes and the second table contains id as the primary key where it keeps a clustered index automatically. I have inserted 100,000 records in both of the tables starting id from 1 to 100,000
When i used to query table 1 it always uses table scan as there is no index
when i used to query table 2 given below
select * from table2 where id <= 5000
select * from table2 where id<=50000
Always it uses index seek only in the execution plan.
When does index scan happen and what is the difference internally. It would be great if someone could explain clearly.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

BOL explains it pretty good

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Or http://sqlinthewild.co.za/index.php/2007/11/15/execution-plan-operations-scans-and-seeks/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 3

GilaMonster (11/15/2010)
Or http://sqlinthewild.co.za/index.php/2007/11/15/execution-plan-operations-scans-and-seeks/

You can tell that's a very old entry - the description of how a heap scan works is wrong!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi