Deferred update instead of direct

Although we dont update the column of unique constraint we might verywell end up with defererred update if you dont pass all the coulumns of PK contraint in where clause of the update query. We have to review all update statements to check if all columns of unique constraint is passed in where clause.

 Assume you fire below query on table with below constraint

 CONSTRAINT [PK_] PRIMARY KEY CLUSTERED
(
 c1  ASC,
 c2 ASC
 )

update table xx set xx=1 where pk1=1 and xx2=2

It is possible that multiple rows will be returned by the where clause in the update,hence breaks the requirements for a direct update (it is considered to be a multirow update because of the where clause)
Althought you did not update PK columns ,your search argument did not specify value for all the columns used to create index ,so you might end with deferred update

Advertisements