订购 SQL Server 列时对性能/空间有何影响？
在设计新表时，对于声明列的顺序是否应考虑任何注意事项？我倾向于将主键放在第一位，然后是任何外键（通常是代理键整数），然后是其他列，但与同事的讨论让我们想知道 SQL Server 是否会填充我们的数据，可能是为了使其更快。
出于性能原因（C++ 编译器在默认条件下对齐结构的方式），SQL Server 是否会尝试将磁盘上的数据（带填充）对齐到特定的字节对齐边界，还是只分配我们的总行所需的字节数（可能在行级别填充）？即，如果我们有一个 3 字节 char 列和另一列 bit/tinyint 类型的列，我们是否可以通过使其中一个列跟随另一个列在 4 字节边界对齐来期望服务器的行为发生任何变化（更好或更差）？ SQL Server 是否关心我声明列的顺序，或者是否可以按照自己认为合适的方式随意排列它们？
我知道在尝试优化表列的布局之前，我可能应该首先查看一百万件事，但出于好奇，我有兴趣知道 SQL Server 是否关心列排序，如果关心的话，在哪里人们会去（DMV，等等？？）看看它是如何在磁盘上物理地布置行的。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
SQL Server 以设定和固定的方式将数据存储在磁盘上。
sys.columns 和键列中的顺序与此磁盘上的顺序无关。
请参阅 “剖析记录”（Paul Randal）以及我的回答：如何达到每行 8060 字节和每个（varchar、nvarchar）值 8000 个字节的限制？
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?