Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
983 views
in Technique[技术] by (71.8m points)

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...