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
Post a Comment