Sql Server性能和字段顺序

does the order of fields creation in a table effect on the performance of commands on the table? If the answer is yes, can anyone discuss it?

表中的字段创建顺序是否会影响表上命令的性能?如果答案是肯定的,那么有人可以讨论吗?

For example i have create a table like this

例如,我创建了一个这样的表

create table Software(int id,alpha datetime,beta datetime,title nvarchar(100),stable datetime,description nvarchar(200) )

if i change it to

如果我把它改成

create table Software(int id,alpha datetime,beta datetime,stable datetime,description nvarchar(200),title nvarchar(100) )

Is there any performance effect ?

有任何性能影响吗?

Is it clear?

清楚吗?

3 个解决方案

#1

The field order makes no difference whatsoever (if the fields are always the same of course)

字段顺序没有任何区别(如果字段总是相同的话)

The on-disk structure will remain the same pretty much regardless. Simply:

无论如何,磁盘上的结构都将保持不变。只是:

header
fixed length columns

固定长度的列

null bitmap
variable length columns

变长列

All you're doing above is rearranging some columns inside the "fixed length" and "variable length" sections. However, the same processing is required to retrieve them no matter which order they are in.

您在上面所做的只是在“固定长度”和“可变长度”部分内重新排列一些列。但是,无论它们处于何种顺序,都需要进行相同的处理来检索它们。

See Paul Randal's article

请参阅Paul Randal的文章

#2

No. This will not affect performance.

不会。这不会影响性能。

#3

-3

The field order can have a very subtle effect on performance and how often maintenance operations will have to take place. In the grand scheme of things, it would be a significantly better use of your time to:

现场订单可能会对性能产生非常微妙的影响,以及维护操作必须经常发生的频率。在宏伟的计划中,将更好地利用您的时间来:

Build/Test indexes
Restructure queries
Partition tables (horizontally & vertically)

分区表(水平和垂直)

Alter the filegroup architecture

改变文件组架构

That is not an exhaustive list.

这不是一个详尽的清单。

You didn't mention if you've already tuned any of those things and are still needing to squeeze some extra ms out of the process.

你没有提到你是否已经调整过任何这些东西,并且还需要在这个过程中挤出一些额外的ms。

#1