May 8, 2012 at 8:59 pm
Hi,
what are the major differences between seek and scan in sql server?
May 8, 2012 at 10:08 pm
May 9, 2012 at 12:05 am
Suresh B. (5/8/2012)
differences between seek and scan in sql server
Thanks for the link Suresh. It was very INFORMATIVE. :-D:hehe::-D:hehe:
Btw Sudha,
Index Scan: Index scan scans all rows of the table (via the index leaf) to match the given condition. Index scan happens when Query Optimizer unable to find a useful index to locate the particular record and determines Index scan will be more efficient than a table scan. Since it touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate. However as the table grows (and the percentage of qualified matching rows reduced), this becomes less and less efficient.
Index Seek: Index seek touches only the row that qualify and pages that contain those qualifying rows. An Index Seek means that the Query Optimizer was able to find a useful index in order to locate the appropriate records. Associate cost of this operation is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table. So this is much better if you have a large table and percentage of qualified matching rows is less.
May 9, 2012 at 5:37 am
Thank you very much
May 9, 2012 at 11:17 pm
May 9, 2012 at 11:55 pm
vinu512 (5/9/2012)
Suresh B. (5/8/2012)
differences between seek and scan in sql serverThanks for the link Suresh. It was very INFORMATIVE. :-D:hehe::-D:hehe:
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply