列排序是否影响Microsoft SQL Server 2012中的性能？ [英] Does column ordering affects performance in Microsoft SQL Server 2012?

问题描述

我读过，varchar字段应该作为列放在数据库表的末尾 - 至少在MySQL中。原因是varchar字段具有可变长度，并且可能会减慢查询速度。
我的问题：这是适用于MSSQL 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?

推荐答案

与数据库设计（实体，属性和关系），事务设计和查询设计的性能影响相比，表中列的顺序对性能的影响非常小。

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.

列的顺序可以影响大小在SQL Server中，当一行中的大量列是可空的，并且这些列中的大多数包含NULL时。 SQL Server（如Oracle）具有优化，其中没有为包含空值的列保留空间AT THE END行。

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.

这样做的好处是，如果你有很多的空间，可空列，您希望最常见的列在最频繁为NULL的列之前。

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.

注意：请记住，SQL Server对表首先通过列是固定长度还是可变长度。所有的固定长度列首先存储，然后是所有可变长度列。在这些列（固定和可变）列中，列按照它们定义的顺序存储。

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.

这篇关于列排序是否影响Microsoft SQL Server 2012中的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！