About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

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: