use tempdb;
go

create table dbo.t (id int, a int);
insert into dbo.t values (1, 0), (1, 2), (2, 3);
create index IX_t__id on dbo.t (id);
go

/*Какие ресурсы будут блокироваться*/
select object_id('dbo.t');
select %%lockres%%, * from dbo.t;
select %%lockres%%, * from dbo.t with (index = IX_t__id);
go

print '------------------------------------------ Table Scan -------------------------------------------------------';
begin tran;
dbcc traceon(3604, 1200, -1) with no_infomsgs;
update t set a = 100 from dbo.t t with (index = 0) where id = 1 and a > 0;
dbcc traceoff(3604, 1200, -1) with no_infomsgs;
rollback;
print '-------------------------------------------------------------------------------------------------------------';

print '------------------------------------------ Index Seek -------------------------------------------------------';
begin tran;
dbcc traceon(3604, 1200, -1) with no_infomsgs;;
update t set a = 100 from dbo.t t with (index = IX_t__id) where id = 1 and a > 0;
dbcc traceoff(3604, 1200, -1) with no_infomsgs;
rollback;
print '-------------------------------------------------------------------------------------------------------------';
go

drop table dbo.t;
go