TEXT DELETE operator

Another type of query plan where DELETE, INSERT, and UPDATE operator can have more than one child operator is the alter table drop textcol command, where textcol is the name of a column whose datatype is text, image, or unitext. This version of command used the TEXT DELETE operator in its query plan. For example:

use tempdb
go
create table t1 (c1 int, c2 text, c3 text)
go
set showplan on
go
alter table t1 drop c2

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using the Abstract Plan in the PLAN clause.

STEP 1
The type of query is ALTER TABLE.

5 operator(s) under root

ROOT:EMIT Operator (VA = 5)

	|INSERT Operator (VA = 52)
	|  The update mode is direct.
	|
	|   |RESTRICT Operator (VA = 1)(0)(0)(3)(0)(0)
	|   |
	|   |   |SCAN Operator (VA = 0)
	|   |   |  FROM TABLE
	|   |   |  t1
	|   |   |  Table Scan.
	|   |   |  Forward Scan.
	|   |   |  Positioning at start of table.
	|   |   |  Using I/O Size 2 Kbytes for data pages.
	|   |   |  With LRU Buffer Replacement Strategy for data pages.
	|
	|   |TEXT DELETE Operator
	|   |  The update mode is direct.
	|   |
	|   |   |SCAN Operator (VA = 3)
	|   |   |  FROM TABLE
	|   |   |  t1
	|   |   |  Table Scan.
	|   |   |  Forward Scan.
	|   |   |  Positioning at start of table.
	|   |   |  Using I/O Size 2 Kbytes for data pages.
	|   |   |  With LRU Buffer Replacement Strategy for data pages.
	|
	|  TO TABLE
	|  #syb__altab
	|  Using I/O Size 2 Kbytes for data pages.

One of the two text columns in t1 is dropped, using the alter table command. The showplan output has the appearance of a select into query plan because alter table internally generated a select into query plan.

The INSERT operator calls on its left child operator, the SCAN of t1, to read the rows of t1, and builds new rows with only the c1 and c3 columns inserted into #syb_altab. When all the new rows have been inserted into #syb_altab, the INSERT operator calls on its right child, the TEXT DELETE operator, to delete the text page chains for the c2 columns that have been dropped from t1.

Postprocessing replaces the original pages of t1 with those of #syb_altab to complete the alter table command.

The TEXT DELETE operator appears only in alter table commands that drop some, but not all text columns of a table, and it always appears as the right child of an INSERT operator.

The TEXT DELETE operator displays the update mode message, exactly like the INSERT, UPDATE, and DELETE operators.