Examples of scanning methods in SQL Server execution plan

Examples of scanning methods in SQL Server execution plan

Original address: http://www.cnblogs.com/zihunqingxin/p/3201155.html

1. How to use the execution plan
Select the statement you want to execute, click Ctrl+l to execute

2. Example
Student table, id,name,address
Build a clustered index on the ID
Name Build Index
Address No Index

3. Difference
1. "table Scan": traverse the entire table to find all matching record rows. This operation will be checked in one line, and of course, the efficiency is the worst.
Search by store order, where address= ' 123 ', as criteria for non-indexed fields

2. "Index Scan": According to the index, filtering out a portion of the records from the table, and then find all the matching row of records, obviously more than the first way to find a range of smaller, so than the "table Scan" faster.
Multiple conditions, with indexed columns + no indexed columns, first identify the range from the index column, and then traverse the range to match the no-index column. Index + table Scan
where name= ' cui ' and address= ' 123 '

3. "index Seek": Based on the index, locate (get) the location of the record, and then get the record, so it is faster than the first two ways.
Individual queries that have indexes. Locate the location through the index, and then find the data. where name= ' Cui '

4. "Clustered Index scan": Same as "Table Scan".
Note: Do not assume that there is an index, it is not the same. In fact, it means to scan each row of records on a per-row basis by a clustered index, because records are stored sequentially in a clustered index. The table scan simply says that the tables to be scanned do not have a clustered index, so the two operations are essentially the same.

5. "Clustered index Seek": Get records directly based on the clustered index, fastest!
where id=1; The clustered index is where it is stored. One step less than 3.