T-SQL Memo (5): View execution plan

Understand several Concepts first: Table scan, clustered index Scan, clustered index lookup, index Scan, bookmark lookup.

"View Execution Plan"

Before you understand the concept, you need to know how to view the execution plan-ctrl+l. Such as:

NOTE:SQL Server execution plans are viewed from right to left.

" Understanding several concepts"

1. Table Scan: occurs in the heap table, and when no available indexes are available, a table scan occurs, indicating that the entire table is scanned once.

SELECT * FROM T_userinfo

View the execution plan as follows:

2. Clustered Index Scan (ClusteredIndex Scan): occurs in a clustered table and is equivalent to a full table scan operation, but is more efficient when conditions such as (where Id > 10) are used for clustered columns

To create a clustered index:

CREATE CLUSTERED INDEX Index_userid on  t_userinfo (Userid)

　　

Because a clustered index is added to a table, it uses a clustered index to find even the entire data.

3. Clustered index Lookup (Clustered index Seek): scans a specific range of rows in a clustered index.

SELECT * from T_userinfo where userid= ' Abcde12ef '

Note: If the lookup condition behind the where has a clustered index column. That is, when and only if the condition behind the where contains a clustered index.

4. Index Scan:The overall scan of nonclustered indexes.

To create a nonclustered index:

CREATE nonclustered INDEX ix_name on T_userinfo (UserName)

Select UserName from T_userinfo---as long as not all columns *

Note: When you do not overwrite and include all of the required columns in a nonclustered index, SQL Server finds the data using the clustered index.

5. Bookmarks lookup (bookmarklookup)

We know that if the table does not have a clustered index, select * from T_userinfo uses a table scan, and some uses a clustered index scan. However, if there are clustered index opportunities, use a clustered index scan.

SELECT * from T_userinfo with (index (ix_name)) where Username= ' Lee 100 '

Here's a quote: The above procedure can be understood as: first find the desired row through a nonclustered index, but this index does not contain all the columns, so you also have to go to the base table to find the columns, so you want to do key lookup.

T-SQL Memo (5): View execution plan