Friday, January 08, 2010

How to update large count of rows without locking them

Found this interesting, how to update large databases, so that you don't have to lock rows
http://blogs.msdn.com/sqlpfe/archive/2010/01/06/tsql-coding-patterns-i.aspx

So instead of the usual:


 UPDATE dbo.Foo
SET Column = 'Value'


Try:


DECLARE @UpdatedRows(PK_Id int NOT NULL PRIMARY KEY)
DECLARE @var INT
SELECT @var=0 -- this resets @@ROWCOUNT=1


WHILE @@ROWCOUNT >0
BEGIN
UPDATE TOP(1500) BAR
  SET Column='Value'
OUTPUT inserted.PK_ID
INTO  @UpdatedRows
FROM  dbo.BAR as BAR
WHERE NOT EXISTS (SELECT 1 FROM @UpdatedRows UPD WHERE UPD.PK_ID=BAR.PK_ID)
END



I prefer one of the ways the commentator offered: (easier to follow)

WHILE @@ROWCOUNT >0
BEGIN
 UPDATE TOP(1500) BAR
   SET Column='Value'
 WHERE
   Column <> 'Value'
END


No comments:

Post a Comment