text delete operator

Another type of query plan where a DML 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. The following queries and query plan are an example of the use of the text delete operator:

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

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

5 operator(s) under root

The type of query is ALTER TABLE.

ROOT:EMIT Operator

	|INSERT Operator
	|  The update mode is direct.
	|
	|   |RESTRICT Operator
	|   |
	|   |   |SCAN Operator
	|   |   |  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
	|   |   |  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 looks like 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. Post-processing 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 deltext operator displays the update mode message, exactly like the update, delete, and insert operators.