Introduction
I received a 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'
The 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 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 (http://blog.SQLAuthority.com)
