TSQL coding patterns I

TSQL coding patterns I

  • Comments 1

With this post I will start writing some small TSQL coding patterns and tricks which make daily life of SQL Server database developer easier and are used a LOT when dealing with large databases.

Data developers frequently have to update rows in tables. If table is small, you might be able to update the entire table using straightforward UPDATE statement like

 UPDATE dbo.Foo

SET Column = 'Value'

However, for very large tables, say with 100,000+ rows this simply does not work because SQL Server will escalate row/page locks to a single table lock and your transaction will lock out users. The trick is to use TOP clause in the UPDATE statement together with OUTPUT clause to keep track of which rows have already been updated so that you don’t update them a second time:

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 

Please note that result of the output clause may be a permanent work table if you expect large number of rows and thus expect to hit scalability limits of table variables. 

Hope you find it useful.

Regards,

-Yuriy 

 

Comments
  • No need to keep track of which rows you've already updated in this case. You can just as easily do:

    WHILE @@ROWCOUNT >0

    BEGIN

     UPDATE TOP(1500) BAR

       SET Column='Value'

     WHERE

       Column <> 'Value'

    END

Page 1 of 1 (1 items)
Leave a Comment
  • Please add 7 and 4 and type the answer here:
  • Post