Assigning to local variable in update statement

Question

What does mean next peace of code:

update dbo.dbr_schem
set @expr=isnull(@expr+' union all ','') 
+'select ' 
+ cast(pozycja as varchar)  + ','
+ cast(schemat as varchar)  + ',' 
+ cast(nardolny as varchar) + ',' 
+ cast(nargorny as varchar) + '' +
          case when warunekSQL<>'' then ' where ' + warunekSQL else '' end
where indeks=@indeks

It looks like simple assigning to the local variable but why it use in update statement ?

I don't know why this would be done in an update. Usually this sort of processing is done using select. — Gordon Linoff
– Gordon Linoff, Commented Feb 21, 2015 at 13:47
@GordonLinoff Is there any diffs between update and select in this case ? — abilash
– abilash, Commented Feb 21, 2015 at 13:49
The update might incur extra overhead for logging. It is probably slightly more expensive than a select, because it has to lock rows for updates, even if nothing changes — Gordon Linoff
– Gordon Linoff, Commented Feb 21, 2015 at 13:53

Solomon Rutzky · Accepted Answer · 2015-02-22 18:04:23Z

2

Updating a local variable in an UPDATE is known as the "quirky update". It is something that is typically used when needing to carry a value across rows of an update in scenarios such as a running total. This was more important of a feature prior to the windowing functions introduced in SQL Server 2012. There is a fairly good discussion of it here that has links to various other articles and forums that would be beneficial to read:

Please can somebody explain how quirky updates work?

In this particular case it does not appear that it provides any benefit over doing this in a SELECT. This is because there is no field of the table being updated at the same time as the variable is being set. Being able to deal with a real field in addition to a variable is not something that can be done in a SELECT statement (i.e. you cannot do SELECT field1, @variable = @variable + field2 FROM Table), which is why the quirky update is sometimes quite handy. Hence, this particular usage could be turned into a SELECT with no functional difference as it is just creating a Dynamic SQL string.

The only possible functional difference in this particular usage between an UPDATE and SELECT would potentially be the order of the values concatenated into the string. The reason is that the quirky update is ordered by the Clustered Index whereas changing this to a SELECT, with no additional changes to add an ORDER BY, will have no guaranteed order.

In terms of logging, since nothing is being updated in this UPDATE statement, nothing is written to the Transaction Log. I have confirmed this by testing the variable-only scenario (i.e. the current question) as well as an UPDATE that does have a field specified in the SET clause but no rows match the WHERE clause. In neither case was anything logged, not even if an explicit transaction was started and committed.

Please note that, with respect to locking, if you are using the SNAPSHOT transaction isolation level, then fewer (and less impacting) locks will be taken when using an UPDATE so there should be little difference between it and using a SELECT (since no rows are actually being updated here). Otherwise, both the UPDATE and SELECT appear to take the same number of locks. The difference, though, is that the UPDATE takes "Exclusive" locks whereas the SELECT only takes "Shared" locks. Since "Shared" locks are less impacting to other processes and allow for more concurrency, given that no actual field in this table is being updated, this query really should be converted to a SELECT:

SELECT @expr = ISNULL(@expr + ' union all ', '') 
+'select ' 
+ cast(pozycja AS VARCHAR(50))  + ','
+ cast(schemat AS VARCHAR(50))  + ',' 
+ cast(nardolny AS VARCHAR(50)) + ',' 
+ cast(nargorny AS VARCHAR(50)) + '' +
          CASE WHEN warunekSQL<>'' THEN ' where ' + warunekSQL
               ELSE ''
          END
FROM dbo.dbr_schem
WHERE indeks = @indeks;

Please note that in the query above, I have specified a length/size for the CAST AS VARCHAR which is otherwise unspecified in the query in the Question. Variable length datatypes should not be left as unspecified as the default is either 1 or 30 depending on the situation in which it is being done.

edited Feb 22, 2015 at 18:04

answered Feb 21, 2015 at 14:33

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Gordon Linoff Over a year ago

The documentation is rather specific: "An UPDATE statement always acquires an exclusive (X) lock on the table it modifies," (msdn.microsoft.com/en-us/library/ms177523.aspx). Do you have a reference that this is bypassed for a quirky update?

Solomon Rutzky Over a year ago

@GordonLinoff I have seen it documented on MSDN and will try to find it and post it here. However, what you found still proves my point since the table here isn't being updated since no actual field is specified in the SET clause. Had it been SET @variable = field = expression then that would be different. And to be clear, this behavior is not specific to the quirky update. I will notify you when I find that documentation.

Gordon Linoff Over a year ago

. . A table lock would typically take place before any rows are found, at the beginning of the transaction. That is a nuance, though. What I'm really curious about is whether SQL Server has special optimizations to support these "quirky updates". If so, there might be a reason why they are preferred (or at least supported) over select. Personally, I see no reason to use update in this context.

Solomon Rutzky Over a year ago

@GordonLinoff I am testing now with SQL Profiler so we do not need to rely on interpretation or memory or possibly erroneous documentation. Though I agree that I see no benefit here for UPDATE as compared to SELECT, outside of possibly to force order, but that is highly unlikely / suspicious.

Dan Guzman Over a year ago

Interestingly, the locking behavior varies depending on the table schema. In the READ_COMMITTED isolation level and a primary key on indeks, I see an exclusive key lock held for the duration of the transaction on the specified key. However, with a heap, I see update locks acquired and released, during the scan. Quirky indeed. Personally, I'd use a SELECT for this task.

|

Collectives™ on Stack Overflow

Assigning to local variable in update statement

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related