sql - Why update clause updates primary key index -


i have query

declare @ids table (     id bigint ); declare @dtdatelimit datetime; set @dtdatelimit = dateadd(minute, (-1) * @resigntimeout, getdate());  update queue_local set [status] = @queuelocalstatustosign output inserted.id @ids [status] = @queuelocalstatussigning , status_date < @dtdatelimit; 

according execution plan clause updates primary key index (on column "id"). there reason behavior?

i'm asking because have deadlock on index similar update

update top (1) queue_local set [status] =                 case                     when @signerror null @queuelocalstatussigned                     else @queuelocalstatuserror                 end output inserted.id @ql_ids task_sign_id = @ts_id , sono = @sono , [status] = @queuelocalstatussigning 

i try understand server behavior

when @ execution plan. see operator clustered index update? if have table clustered index clustered index containts whole table. needs updated. on other hand doesn't mean, clustered index key updated. if, in case, column [status] not part of clustered index key, leaf level of clustered index updated matching rows. (if no page splits occur).

if have nonclustered index on [status] column, sql server can lookup entry point (id) clustered index there.


Comments