订购 SQL Server 列时的性能/空间影响? [英] Performance / Space implications when ordering SQL Server columns?

问题描述

在设计新表时，是否应该考虑列的声明顺序?我倾向于将主键放在第一位，然后是任何外键(通常是代理键整数)，然后是其他列，但是与同事的讨论让我们想知道 SQL Server 是否会填充我们的数据，可能会使其更快.

出于性能原因(C++ 编译器在默认条件下对齐结构的方式)，SQL Server 是否会尝试将我们在磁盘上的数据(使用填充)对齐到特定的字节对齐边界，还是只分配与我们的字节数一样多的字节?总行需要(可能在行级别填充)?IE.如果我们有一个 3 字节的 char 列和另一个类型为 bit/tinyint 的列，我们是否可以通过使其中一个跟随另一个以 4 字节边界对齐来期望服务器的行为发生任何变化(更好或更坏)?SQL Server 甚至关心我声明列的顺序，还是可以按照它认为合适的方式自由地布置它们?

我知道在尝试优化表列的布局之前，我应该首先考虑一百万件事情，但出于好奇，我很想知道 SQL Server 是否关心列排序，以及所以，人们会去哪里(DMV 等??)看看它是如何在磁盘上实际布置行的.

解决方案

SQL Server 以固定方式将数据存储在磁盘上.

sys.columns 和关键列中的顺序与此磁盘顺序无关.

参见 记录剖析"(Paul Randal)和我的回答:如何达到每行 8060 字节和每 (varchar, nvarchar) 值 8000 的限制?>

Are there any considerations that should be taken into account when designing a new table with regards to the order in which columns should be declared? I tend to put the primary key first, followed by any foreign keys (usually surrogate key integers), followed by other columns, but a discussion with a colleague had us wondering whether SQL Server will pad our data, possibly to make it faster.

Will SQL Server try and align our data on disk (with padding) to a specific byte alignment boundary for performance reasons (the way a C++ compiler would align a struct under default conditions) or will it just allocate as many bytes as our total row requires (possibly padding at a row level)? I.e. if we have a 3 byte char column and another column of type bit / tinyint, could we expect any change in behaviour (better or worse) from the server by making one of these follow another to align at 4 byte boundaries? Does SQL Server even care what order I declare the columns, or is it free to lay them out as it sees fit?

I understand that there are probably a million things I should look to first before trying to optimise the layout of my table columns, but for curiosity's sake I'm interested to know whether SQL Server cares about column ordering at all, and if so, where one would go to (DMV, etc??) see how it's physically laying the rows out on disk.

解决方案

SQL Server stores the data on disk in set and fixed fashion.

The order in sys.columns and key columns has no relevance to this on-disk order.

See "Anatomy of a record" (Paul Randal) and my answer here: How do you get to limits of 8060 bytes per row and 8000 per (varchar, nvarchar) value?

这篇关于订购 SQL Server 列时的性能/空间影响?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！