I recently received few comments that I have not written on simple subjects recently. In fact, this blog is dedicated to all those who are really learning SQL Server and almost all the articles and posts are posted here keeping this goal in mind. One of the questions in the email which requested to write simple subjects was “Does the order of columns in UPDATE statements matter?” Let me try to answer this question today.
The question in detail:
Does the order of the columns in UPDATE statements matter?
For example, is there any difference between option 1 and option 2 of UPDATE statement?
Option 1:
UPDATE TableName
SET Col1 = 'Value', Col2 = 'Value2'
Option 2:
UPDATE TableName
SET Col2 = 'Value2', Col1 = 'Value'
Answer is NO. There is no difference between them in SQL Server.
There are two related posts on this subject, which I had written previously. I suggest you all to read them as well. They are pretty old but still valid. I am looking for some suggestions for the same articles too.
SQL SERVER – Transaction and Local Variables – Swap Variables – Update All At Once Concept
Update All at Once concept is purely based on Atomicity (link goes to Wikipedia). In an atomic transaction, a series of database operations, either all of them occur, or nothing occurs. A guarantee of atomicity prevents updates to the database occurring only partially, which can cause greater problems than rejecting the whole series outright.
SQL SERVER – Change Order of Column In Database Tables
Well, I strongly advocate that order of columns in database table should not matter. As a matter of fact, while searching for the subject, I end up on my own blog where I have previously suggested why it was necessary. I suggest you to make the right decision based on your business need.
Reference : Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
I may know why this question was emailed in. For anyone using OLEDB databases (like MS Access) order DOES matter. This requirement mandates that coders are very careful, particularly if they create parameter collections, or they run the risk of some nasty side-effects.
For example,
UPDATE TableName SET Col1= @Col1, Col2 = @Col2
When creating the @Col1 and @Col2 OLEDB parameters you must make sure the parameters are created in the same order to match their order in the actual SQL statement.
Sorry for getting off-track of your ‘simple’ examples above but I ran into errors in OLEDB in the past and perhaps this will help someone out there. For the record, it is a very welcome change that SQL Server doesn’t have an order restriction!!
I have something from today – that “Seems” to say otherwise. I would LOVE to find the real cause – but having the application change the order of the SET statements “fixed” the problem. But I don’t understand why.
Env:
VMWare (current)
SQL 2008 Enterprise
2 servers 1 and server 2 – identical
Simple P2P – low volumne testing
distribution DB’s on respective servers
Both servers physcially next to each other
On Server 1
Update X
Set A = “date1”,
B = “date2”
Where ab in (subselect)
Data appears on Server 2 as above – and we see the transactions in both Profilers to support it (at the DB and Distribution DB)
Then : sill on Server 1
Update X
Set B = “date3”,
A = “date1”
Where ab in (subselect)
Data appears on Server 2 as above – and we see the distribution transactions to support it
BUT on Server 1 – we see the update statment as defined and it gets sent to Server 2, but the DATA on Server 1 in the date field is “something else”.
When I had them make the order the same – the problem disappeared.
If anyone has any comments on why – and what is the “real” operation that cause the problem.
Note: the checksum’s didn’t match – unless we ran the update in the 1st case – after the 2nd update – the checksums were different (as well as the data appearing differently).
Also checked for “with rowlock” and
“with nolock”
Stumped.
Jack
I read this post because it was at the top of my google serach when I typed this question. This answer isn’t completely accurate.
The order of columns can be very useful, and it does seem to execute them in order. I have a database that I must track their last email address … and because order does matter, I am able to do this with one statement.
SQL:
update myUsers set oldEmail=currentEmail, currentEmail=@newEmail where theUserId=@theUserId;
This could also be good for recalculating totals for a column … make the changes first, and have the last column update as the last update column in the statement.
((YES YES, I know that some of these changes can happen within triggers too if you decide you want to create them … I prefer managing and using the dynamic sql cache for my statement written in code))
Blah, disregard my last statement … I started testing this across various database engines, and this is important to note …
It doesn’t matter, you were right and I didn’t test it across the board.
It completes the update based on the previous values.
So SQL:
update tbl set col1 = ‘asdf’;
then:
update tbl set col1 = ‘qwer’, col2=col1;
THEN:
col2 = ‘asdf’
Although, I should have ran this test before … I believe this is all worth noting for others that may use this type of update for logging purposes.
The database engine seems to complete all of the column updates based on the current column values before committing.
Take care all.
how about calculated values or using other col which is updated in the same query
for example :
update table1 set col1=col2+2, col2=col2+10