CODEWITHSUNDEEP
sql-server
Ilia G
Ilia G

Reputation: 10211

Does the order of columns in the table matter?

We have a number of projects big and small - most (if not all) of them use at least one SQL Server DB. All of them have different environments set up. Typically: dev (1+), QA, UAT, Live. It is also common for us to release various code updates to different environments independently of each other. Naturally some of those updates come with schema update scripts such as 

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

Sometimes made by hand, other times using Red Gate SQL/Data Compare.

Anyway where I'm going with this is that often different environments for the same project end up with different order of columns. Is this a problem? I don't really know... Does column order have any performance implications? Anything I could be missing?

Upvotes: 10

Views: 3813

Answers (2)

marc_s
marc_s

Reputation: 754598

Not really - in 95% of your cases, there's no difference in the column ordering. And from a relational theoretical point of view, column order in a table is irrelevant anyway.

There are a few edge cases where column order might have a slight impact on your table, most often when you have a large number of variable size fields (like VARCHAR). But that number needs to be really large, and your fields (their size) needs to be really massive - in such a case, it can be beneficial to put those variable size fields at the end of the table in terms of ordering the columns.

But again: that's really more of a rare edge case, rather than the norm.

Also, mind you: SQL Server has no means of reordering columns, really. You can do that in the visual table designer - but what SQL Server does under the covers is create a new table with the desired column ordering, and then all the data from the old table is copied over. That's the reason this is a very tedious and time consuming operation for large tables.

Upvotes: 3

Charles Bretana
Charles Bretana

Reputation: 146499

No, column order is not significant. In actuality, the order that column data is stored on disk may itself be different than the order you see in client tools, as the engine reorders the data to optimize storage space and read/write performance (putting multiple bit fields into a single memory location, aligning columns on memory boundaries, etc.. )

Upvotes: 9

Related Questions