8060字节在8060 B数据页（SQL Server）？ [英] 8078 bytes in 8060 B datapage (SQL Server)?

问题描述

无处不在，SQL Server中的数据以8K（8192 B）的页面写入，每个数据的字节数为8060字节，其余的是开销（系统信息）。

尽管最近的文章[1]给出了代码示例，据我所知，8078字节的数据适合于一个页面。

我在每个页面上理解8,060 B是什么意思？

我验证了x86 SQL Server上的代码2008 R2 ...

---

更新：

看一个关于[1]的后续行动的答案？我很遗憾，我没有把这标记为有帮助（对我而言），立即评论...我只是想在回应之前调查自己...

---

Update2：

我发布了subquestion [2]

[1] br>
表设计如何影响您的SQL Server性能？

http://sqlserver-training.com/how-table-design-can-impact-your-sql-server-performance/-

[2]

如何达到每行8060字节的限制，每个（varchar，nvarchar）为8000？

< a href =http://stackoverflow.com/questions/3793022/how-to-come-to-limits-of-8060-bytes-per-row-and-8000-per-varchar-nvarchar>你好吗达到每行8060个字节的限制和每个（varchar，nvarchar）值的8000个值？

解决方案

长回答短，限制为每行8060字节，但8096字节每页您链接的文章中的行的行大小为约4000字节，因此它们在每行的限制之下。但是，这并不能解决这样一个页面上有多少这样的问题。

请参阅联机丛书中的估计堆的大小：

http://msdn.microsoft .com / en-us / library / ms189124.aspx

如果您对文章中的表进行计算，您将看到第一个表的物理行大小为4048字节，这是页面8096限制的一半。

It is written everywhere that data in SQL Server is written in pages of 8K (8192 B) each with 8060 bytes for data and the rest is overhead (system info).

Though, the recent article [1] gives code example illustrating, as I understood, that 8078 bytes of data fit into a page.

What do I miss in understanding 8,060 B per per page?

I verified the code on x86 SQL Server 2008 R2...

---

Update:

Did I see an answer telling about follow-ups to [1]? I pity that I did not mark that as helpful (to me) and comment immediately... I just wanted to investigate more myself before responding...

---

Update2:

I posted subquestion [2]

[1]
How table design can impact your SQL Server performance?
http://sqlserver-training.com/how-table-design-can-impact-your-sql-server-performance/-

[2]
How to come to limits of 8060 bytes per row and 8000 per (varchar, nvarchar)?
How do you get to limits of 8060 bytes per row and 8000 per (varchar, nvarchar) value?

解决方案

Long answer short, the limit is 8060 bytes per row, but 8096 bytes per page. The rows in the article you linked have a row size of ~4000 bytes, so they are well under the limit per row. However, that does not answer the question of how many such rows fit on a page.

See "Estimating the size of a heap" in Books Online:

http://msdn.microsoft.com/en-us/library/ms189124.aspx

If you do the calculation for the tables in the article, you'll see that the first table has a physical row size of 4048 bytes, which is exaclty half the 8096 limit for a page.

这篇关于8060字节在8060 B数据页（SQL Server）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！