If you've looked at any insert, update, or delete plans, including those used in some of my posts, you've probably noticed that nearly all such plans include a top operator. For example, the following update statement yields the following plan:
CREATE TABLE T (A INT)
INSERT T VALUES (0)
INSERT T VALUES (1)
INSERT T VALUES (2)
UPDATE T SET A = A + 1
Rows Executes
3 1 UPDATE [T] set [A] = [A]+@1
3 1 |--Table Update(OBJECT:([T]), SET:([T].[A] = [Expr1004]))
0 0 |--Compute Scalar(DEFINE:([Expr1004]=[T].[A]+[@1]))
3 1 |--Top(ROWCOUNT est 0)
3 1 |--Table Scan(OBJECT:([T]))
What is that top operator doing right above the table scan?
It is a ROWCOUNT top. It is used to implement SET ROWCOUNT functionality. The "est 0" indicates that SET ROWCOUNT was 0 when the query was compiled. (I suppose "est" is short for "estimate" though the value at compilation time has no impact on query optimization or execution.) Recall that a value of 0 means return or update all rows. Since SET ROWCOUNT was 0 at execution time as well, we can see from the STATISTICS PROFILE output that all 3 rows were updated.
Now try the following:
SET ROWCOUNT 1
UPDATE T SET A = A + 1
Rows Executes
1 1 UPDATE [T] set [A] = [A]+@1
1 1 |--Table Update(OBJECT:([T]), SET:([T].[A] = [Expr1004]))
0 0 |--Compute Scalar(DEFINE:([Expr1004]=[T].[A]+[@1]))
1 1 |--Top(ROWCOUNT est 0)
1 1 |--Table Scan(OBJECT:([T]))
Although we get the same plan including the ROWCOUNT top with the same "estimate," this time SET ROWCOUNT was 1 at execution time, the top returned only one row from the table scan, and we can see that only 1 row was updated.
If we force a recompile, we see that the value of the "estimate" changes:
SET ROWCOUNT 1
UPDATE T SET A = A + 1 OPTION (RECOMPILE)
Rows Executes
1 1 UPDATE T SET A = A + 1 OPTION (RECOMPILE)
1 1 |--Table Update(OBJECT:([T]), SET:([T].[A] = [Expr1004]))
0 0 |--Compute Scalar(DEFINE:([Expr1004]=[T].[A]+(1)))
1 1 |--Top(ROWCOUNT est 1)
1 1 |--Table Scan(OBJECT:([T]))
Why doesn't SQL Server add a ROWCOUNT top to select statements?
For example, the following query plan does not include a top yet only returns 1 row:
SET ROWCOUNT 1
SELECT * FROM T
Rows Executes
1 1 SELECT * FROM T
1 1 |--Table Scan(OBJECT:([T]))
SQL Server implements SET ROWCOUNT for select statements by simply counting and returning the correct number of rows from the root of the plan. Although this strategy might work for a really trivial update plan such as the one above, it would not work for more complex update plans. For instance, if we add a unique index to our table, the update plan becomes substantially more complex:
CREATE UNIQUE INDEX TA ON T(A)
UPDATE T SET A = A + 1
Rows Executes
2 1 UPDATE [T] set [A] = [A]+@1
2 1 |--Index Update(OBJECT:([T].[TA]), SET:([Bmk10061024] = [Bmk1006],[A1025] = [T].[A]))
2 1 |--Collapse(GROUP BY:([T].[A]))
2 1 |--Filter(WHERE:(NOT [Expr1021]))
2 1 |--Sort(ORDER BY:([T].[A] ASC, [Act1023] ASC))
2 1 |--Split
1 1 |--Table Update(OBJECT:([T]), SET:([T].[A] = [Expr1004]))
1 1 |--Compute Scalar(DEFINE:([Expr1021]=[Expr1021]))
0 0 |--Compute Scalar(DEFINE:([Expr1021]=CASE WHEN [Expr1005] THEN (1) ELSE (0) END))
0 0 |--Compute Scalar(DEFINE:([Expr1004]=[T].[A]+(1), [Expr1005]=CASE WHEN [T].[A] = ([T].[A]+(1)) THEN (1) ELSE (0) END))
1 1 |--Top(ROWCOUNT est 1)
1 1 |--Table Scan(OBJECT:([T]))
I'm not going to try in this post to explain all of the details of the above plan. I'll save that for a future post. However, observe that in updating 1 row, the root of this plan returns 2 rows. Counting 1 row from the root of this plan would not achieve an accurate result. By placing the ROWCOUNT top above the table scan, the optimizer can ensure that the server updates exactly the correct number of rows regardless of the complexity of the remainder of the plan.
Microsoft