SQL SERVER – Order of Columns in Update Statement Does not Matter

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.

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.

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.

Share

About the Author

Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

Comments and Discussions

A practical piece of advice. It might be worth pointing out that in the physical table, the ordering of columns is not the order in which they are defined. In a physical row, the fixed-length columns appear first, and then all the variable-length columns.