Does the order of the columns in the table mean?

Question

We have several large and small projects - most (if not all) of them use at least one SQL Server database. They all have different settings. Usually: dev (1+), QA, UAT, Live. We can also release various code updates to different environments independently of each other. Naturally, some of these updates contain schema update scripts, such as

alter table foo add column bar go update foo set bar=... where ... 

Sometimes done manually, sometimes Red Hat SQL / Data Compare is used.

In any case, where I'm going to is that often different environments for the same project end up in different column orders. This is problem? I don’t know ... Does the order of the columns have any performance implications? Anything I was missing?

Answer 1

No, the order of the columns is small. In fact, the order of data storage on a column may be different than the order that you see in client tools, since the mechanism reorders the data to optimize memory space and read / write performance (by placing several bits of fields in a single memory cell, aligning columns at the memory boundaries etc.)

Answer 2

Actually - in 95% of cases there is no difference in the ordering of the columns. And from a relational theoretical point of view, the order of the columns in the table does not matter anyway.

There are several edge cases where the order of the columns may have a small effect on your table, most often when you have a large number of variable size fields (e.g. VARCHAR). But this number should be really large, and your fields (their size) should be really massive - in this case it is useful to add these fields of variable size at the end of the table in the order of the columns.

But then again: this is indeed a rarer case of the region, and not the norm.

Also, note: SQL Server does not have a means to reorder columns. You can do this in the visual table designer - but what SQL Server does under the covers is to create a new table with the necessary column ordering, and then all the data from the old table will be copied. This is the reason this is a very tedious and time-consuming operation for large tables.