Updating a large table: set @@rowcount versus explicit transactions

[louis]

When I need to perform an update against multi-million row table I
typically specify @@rowcount, to reduce locks.

e.g.
set @@rowcount 1000
while exists (select * from myTable where col2 is null)
update myTable
set col2 = col1 + 'blahblah'
where col2 is null

However, my boss' script does something like this. I think it works OK
but it seems overly complicated to me. Any thoughts?

while exists (....)
begin tran

insert into #table
select ...

update myTable
set ...
from myTable join #table ...

(@numberOfRows is a counter variable, tracking #rows that have been
updated since last batch)

if @numberOfRows > 1000
begin
commit
begin tran
end

end

[David Portas]

I would prefer:

SET ROWCOUNT 1000

WHILE 1=1
BEGIN
UPDATE myTable
SET col2 = col1 + 'blahblah'
WHERE col2 IS NULL
IF @@ROWCOUNT = 0 BREAK
END

Although a batch size of 1000 seems a bit small for multi-million row table.

I don't see any advantages of the temp table version.

--
David Portas
SQL Server MVP
--

[louis]

Cool. That's even more efficient. Thanks David.