DELETE, INSERT, and UPDATE operators

The DELETE, INSERT, and UPDATE operators usually have only one child operator. However, they can have as many as two additional child operators to enforce referential integrity constraints and to deallocate text data in the case of alter table drop of a text column.

These operators modify data by inserting, deleting, or updating rows belonging to a target table.

Child operators of DML operators can be SCAN operators, JOIN operators, or any data streaming operator.

The data modification can be done using different update modes, as specified by this message:

The Update Mode is <Update Mode>.

The table update mode may be direct, deferred, deferred for an index, or deferred for a variable column. The update mode for a worktable is always direct.

The target table for the data modification is displayed in this message:

	TO TABLE
	<Table Name>

Also displayed is the I/O size used for the data modification:

	Using I/O Size <N> Kbytes for data pages.

The next example uses the DELETE operator:

use pubs2
go
set showplan on
go
delete from authors where postalcode = '90210'

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is DELETE.

2 operator(s) under root

ROOT:EMIT Operator (VA = 2)

	|DELETE Operator (VA = 1)
	|  The update mode is direct.
	|
	|   |SCAN Operator (VA = 0)
	|   |  FROM TABLE
	|   |  authors
	|   |  Table Scan.
	|   |  Forward Scan.
	|   |  Positioning at start of table.
	|   |  Using I/O Size 4 Kbytes for data pages.
	|   |  With LRU Buffer Replacement Strategy for data pages.
	|
	|  TO TABLE
	|  authors
	|  Using I/O Size 4 Kbytes for data pages.