Execution plan for Update statement in sql server

UPDATE statements are against one table at a time. Depending on the structure of the table, and the values to be updated.

See the update statement in

UPDATE NewAddress SET [City] = 'Pune', [ModifiedDate] = GETDATE() WHERE [City] = 'Monroe' ;

See the execution plan

Let's begin reading this execution plan in the physical operation order, from right to left. The first operator is a Non Clustered Index Scan, which retrieves all of the necessary rows from a non-clustered index, scanning through them, and one row at a time. This is not particularly efficient and should be a flag to you that perhaps the table needs better indexes to speed performance. The purpose of this operator is to identify all the rows WHERE [City] = 'Monroe', and then send them to the next operator.

The next three operators are all Compute Scalar operators, which we have seen before. In this case, they are used to evaluate expressions and to produce a computed scalar value, such as the GETDATE () function used in the query.

Now we get to the core of the UPDATE statement, the Table Update operator. In this case, the values being updated are part of a clustered index. So this operator identifies the rows to be updated, and updates them. Last of all, we see the generic T-SQL Language Element catchall operator, which tells us that an UPDATE operation has been completed.