5To execute the T-SQL statement for a clustered index table:
Run the following script:
Use [testdb]
Go
Alter table dbo. Nums add Constraint
Pk_nums primary key clustered
(
N
) With (statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks = on, allow_page_locks = on) on [primary]
Go
5.1Statement: Select N from [testdb]. [DBO]. [Nums]
Select the actual execution plan, and we will see:
Clustered index Scan): Clustered index scan operator scans the clustered index specified in the argument column. When an optional where :() predicate exists, only the rows that satisfy the predicate are returned. If the argument column contains the ordered clause, it indicates that the query processor has requested to return row output in the order of clustered index rows. If there is no ordered clause, the storage engine will scan the index in the best way without generating the sorting output. Clustered index scan is both a logical and physical operator.
5.2And statement: insert into [testdb]. [DBO]. [Nums] ([N]) values (1000001)
Select the actual execution plan, and we will see:
Clustered Index insert): Clustered Index insert operator inserts a row from its input into the clustered index specified in the argument column. The argument column also contains a set :() predicate indicating the values set for each column. If clustered Index insert does not have a child-level insert value, the inserted row is obtained from the insert operator itself. Clustered Index insert is a physical operator.
5.3Statement: update [testdb]. [DBO]. [Nums] set n = 1000002 where n = 1000001
Select the actual execution plan, and we will see:
Clustered Index Update): Clustered Index Update operator is used to update the input rows in the clustered index specified in the argument column. If the where :() predicate appears, only the rows that satisfy the predicate are updated. If set :() is displayed, each updated column is set to this value. If define :() is displayed, the values defined by this operator are listed. These values can be referenced in the set clause, other locations in the operator, and other locations in the query. Clustered Index Update is both a logical and physical operator.
5.4Statement: delete from [testdb]. [DBO]. [Nums] Where n = 1000002
Select the actual execution plan, and we will see:
Delete a clustered index (clustered index delete): Clustered index Delete operator deletes rows from the clustered index specified in the argument column. If the where :() predicate exists in the argument column, only the rows that meet the predicate are deleted. Clustered index Delete is a physical operator.
5.4Statement: Select N from [testdb]. [DBO]. [Nums] Where n = 1000000
Select the actual execution plan, and we will see:
Clustered index search (clustered index seek): The clustered index seek operator can use the index search function to retrieve rows from the clustered index. The argument column contains the name of the clustered index being used and the predicate of seek. The storage engine only uses indexes to process rows that satisfy the seek :() predicates. It also includes where :() predicates, where the storage engine computes all rows that satisfy the seek :() predicates, but this operation is optional and is not completed using indexes. If the argument column contains the ordered clause, it indicates that the query processor has decided to return rows in the order of clustered index sorting rows. If there is no ordered clause, the storage engine searches for indexes in the best way without sorting the output. If the output can be kept in order, the efficiency may be lower than the efficiency of generating non-sorted output. When the keyword lookup is displayed, the bookmarks are searched. Clustered index seek is both a logical and physical operator.