Graph learning in SQL Server graph execution plan (1)

 

SQL ServerGraph execution plan icon Learning (1)

1, Create a sample database (testdb ):

Use [Master]

Go

 

If exists (Select name from SYS. databases where name = n 'testdb ')

Drop database [testdb]

 

Create Database [testdb]

 

2Create a new table (Nums, which is derived from SQL Server 2005 Technical Insider: Query ):

Use [testdb]

Go

 

If object_id ('dbo. nums') is not null

Drop table DBO. Nums;

Go

Create Table DBO. Nums (N int not null primary key );

Declare @ Max as int, @ RC as int;

Set @ max = 1000000;

Set @ rc = 1;

 

Insert into Nums values (1 );

While @ rc * 2 <= @ Max

Begin

Insert into DBO. Nums select N + @ RC from DBO. Nums;

Set @ rc = @ rc * 2;

End

 

Insert into DBO. Nums

Select N + @ RC from DBO. Nums where N + @ RC <= @ Max;

Go

 

3, We delete clustered Index

Use [testdb]

Go

 

If exists (select * From SYS. indexes where object_id = object_id (n' [DBO]. [Nums] ') and name = n' pK _ nums ')

Alter table [DBO]. [Nums] Drop constraint [pk_nums]

 

4To execute the T-SQL Statement on the non-index table:

4.1Statement: Select N from [testdb]. [DBO]. [Nums]

Select the actual execution plan, and we will see:

 

Select

: The result icon used by SQL Server. The result operator is the data returned when the query plan ends. It is usually the root element of the display plan. Result is a language element.

Table Scan(Table scan): This operator Retrieves all rows from the table specified in the parameter (argument) column. If the where :() predicate exists in the argument column, only the rows that satisfy the predicate are returned. It is both a logical operator and a physical operator.

 

Arrow: Indicates the data flow direction, and the height indicates the data size.

4.2And statement: insert into [testdb]. [DBO]. [Nums] ([N]) values (1000001)

Select the actual execution plan, and we will see:

Insert: The generic icon is used. When the appropriate iterator icon cannot be found in the logic of the generated graphic display plan, the generic icon is displayed. There are three common icons: Blue (for iterator), orange (for cursor), and green (for Transact-SQL syntax ).

 

Table insert): The table insert operator inserts the input rows into the table specified in the argument column. The argument column also contains a set :() predicate indicating the values set for each column. If table insert does not have a child-level insert value, the inserted rows are obtained from the insert operator itself. Table insert is a physical operator.

 

4.3Statement: update [testdb]. [DBO]. [Nums] set n = 1000002 where n = 1000001

Select the actual execution plan, and we will see:

Update: Same as insert.

 

Table update): The table update physical operator is used to update the input rows in the table specified in the argument column. Set :() the predicate is used to determine the value of each updated column. These values can be referenced in the set clause, other locations in the operator, and other locations in the query.

 

Top): Top operator scans input, but returns the first specified number of rows or row percentage based on the sorting order. The argument column can contain a list of columns to check duplicate values. In an update plan, the top operator is used to enforce the row count limit. Top is both a logical operator and a physical operator.

 

Table Scan): Same as above. The table to be conditioned is scanned.

 

4.4Statement: delete from [testdb]. [DBO]. [Nums] Where n = 1000002

Select the actual execution plan, and we will see:

Delecte: Same as insert.

 

Table Delete): The table delete physical operator is used to delete rows from the table specified in the parameter column.