1

Topic: Question on operator Compute Scalar for Update request

Hello;
There is an execution plan update request updating a key not unique index:

use tempdb
go
if object_id (' test_upd ')> 0
drop table test_upd
create table test_upd (id int identity, id2 int, constraint pk primary key (id))
create index ni_id2 on test_upd (id2)
insert into test_upd (id2) values (1)
go
set showplan_all on
go
update test_upd set id2 = 100
go
set showplan_all off
go
|--Clustered Index Update (OBJECT:( [tempdb]. [dbo]. [test_upd]. [pk]), OBJECT:( [tempdb]. [dbo]. [test_upd]. [ni_id2]), SET:( [tempdb]. [dbo]. [test_upd]. [id2] = [Expr1006]))
|--Compute Scalar (DEFINE:( [Expr1007] = [Expr1007]))
|--Compute Scalar (DEFINE:( [Expr1007] =CASE WHEN [Expr1004] THEN (0) ELSE (1) END))
|--Compute Scalar (DEFINE:( [Expr1006] = (100)))
|--Compute Scalar (DEFINE:( [Expr1004] =CASE WHEN [tempdb]. [dbo]. [test_upd]. [id2] = (100) THEN (1) ELSE (0) END))
|--Clustered Index Scan (OBJECT:( [tempdb]. [dbo]. [test_upd]. [pk]))

Apparently from plan Clustered Index Update uses Expr1006, but I can not understand as it uses value Expr1007.
Whether correctly I understand, what from the plan it is impossible to see that as operator Clustered Index Update uses value Expr1007?
In advance I thank for answers.

2

Re: Question on operator Compute Scalar for Update request

The probable;
Unfortunately, the plan is often insufficiently informative. But I can tell to you that this such.
At you the row-wise plan of update per row/narrow plan), the server updates the table line by line for each index. Clustered index, further not cluster on id2. But what for it (a key ) to update, if updated value is equal to that value on which it is updated, i.e. if 100 = 100. The server checks it, beforehand calculating in CASE, whether is equal id2 to value 100.
Can see this check if to force  (per index or wide plan) the plan.

...
update test_upd set id2 = 100 option (querytraceon 8790);
...