mysql - Does column ordering affects performance in Microsoft SQL Server 2012?

Question

I have read that the varchar fields should have placed as a column at the end of a database table - at least in MySQL. The reason is because the varchar fields have variable length and it could possibly slow down the queries. My question: is this applies to MSSQL 2012 or not? Should I design my tables to have every textual data at the end of every database row or not?

Answer 1

The order of columns in a table will have a very small impact on performance, as compared to the performance impact of your database design (entities, attributes and relationships), your transaction design and your query design.

To tell if the difference is non-negligible, you'd really need to setup some tests, and compare the results.

Typically, I put the primary key as the first column, then the foreign key(s), and then natural keys and frequently accessed columns. I typically put the longer strings towards the end of the row. But this isn't necessarily a performance optimization, as much as it is a style preference which I use for convenience.

The order of columns can have an impact on the size of the row in SQL Server, when a large number of columns in a row are nullable and most of those columns contain NULL. SQL Server (like Oracle) has optimization where no space is reserved for columns that contain NULL values AT THE END of the row. Some space is reserved for every column in the row, up to the last non-NULL value in the row.

The takeaway from that is that if you have a lot of nullable columns, you want the columns that are most frequently not NULL BEFORE the columns that are most frequently NULL.

NOTE: Keep in mind that SQL Server orders the columns within a table first by whether the column is fixed length or variable length. All of the fixed length columns are stored first, then followed by all of the variable length columns. Within those sets of columns (fixed and variable), the columns are stored in the order they are defined.