不能创建一个大小为8937的行，大于允许的最大值8060 [英] Cannot create a row of size 8937 which is greater than the allowable maximum of 8060
问题描述
我们在数据库中的表格上收到此错误：
We are getting this error on a table in our database:
无法创建大小为8937的行，
最大为8060。
Cannot create a row of size 8937 which is greater than the allowable maximum of 8060.
该表由约400个 varchar（max）字段。但是，我们只将空字符串插入到这些字段中。
The table consists of about 400 varchar(max) fields. We are, however, only inserting empty strings into these fields.
插入似乎有效，但是使用 SqlXml 读取数据或在表的主键上运行 DBCC DBREINDEX 时出现错误。
The insert seems to work, however when using SqlXml to read the data or when running DBCC DBREINDEX on the primary key of the table, the error occurs.
它只发生在一个特定的 SQL Server（2005）而不是其他（2005 Express）上。问题机器正在运行 64位Windows ，其他正在运行 32位窗口。
It is only occurring on one particular SQL Server (2005) and not on others (2005 Express). The problem machine is running 64-bit Windows and the others are running 32-bit windows.
有人有任何想法吗？请让我知道，如果我需要包含任何更多的信息。
Has anyone got any ideas about this? Please let me know if I need to include any more information.
阅读了一些答复后，我想指出，我完全同意，这是非常极端，不寻常的，并不是任何明智的尝试使用这个许多varchar（最大）列。有理由，主要不在我的控制之下，我不会进入这里。
Having read some of your responses, I'd like to point out that I completely agree that it is rather extreme, unusual and not at all sensible to be attempting to use this many varchar(max) columns. There are reasons for it, mainly not under my control, that I will not go into here.
推荐答案
错误是由于您不能在SQL Server中大于8KB（大小为1页）的行，因为行不允许跨页 - 它是SQL Server的基本限制，您可以在此处阅读更多信息：
The error is caused because you cannot have a row in SQL server which is larger than 8KB (the size of 1 page) because rows are not allowed to span pages - its a basic limit of SQL Server, you can read more about it here:
- Database Basics Quick Note - The difference in Varchar and Nvarchar data types
请注意，SQL服务器将允许你要创建表，但是如果您尝试实际插入跨越多个页面的任何数据，那么它将给出上述错误。
Note that SQL server will allow you to create the table, however if you try to actually insert any data which spans multiple pages then it will give the above error.
当然这并不完全添加因为如果以上是真相，那么单个 VARCHAR（8000）列将在表中填满一行！ （以前是这种情况）。 SQL Server 2005解决了这一限制，允许一行中的某些数据存储在另一个页面中，而代替留下24位指针。您可以在这里阅读：
Of course this doesn't quite add up, because if the above was the whole truth then single VARCHAR(8000) column would fill a row in a table! (This used to be the case). SQL Server 2005 got around this limitation by allowing certain data from a row to be stored in another page, and instead leaving a 24-bit pointer instead. You can read about this here:
- How Sql Server 2005 bypasses the 8KB row size limitation
- Maximum Row Size in SQL Server 2005 to the Limit
正如你所看到的，现在意味着行现在可以跨越多个页面，但单列行仍然需要适合单个页面（因此列的最大大小为 VARCHAR（8000）），并且您可以拥有的这些列的总数仍然有限制（约8000/24 =〜300我的估计）
As you can see this now means that rows can now span multiple pages, however single column rows still need to fit into a single page (hence the maximum size of a column being VARCHAR(8000)) and there is still a limit on the total number of such columns you can have (around 8000 / 24 = ~300 by my estimate)
当然，这一切都缺少要点，也就是说，一张桌子上的400个宽列是荒谬的!!!
Of course this is all missing the main point, which is that 400 wide columns on a single table is absurd!!!
你应该长期看看你的数据库模式，并提出一些更合理的东西 - 你可以先从列大小中选择一些比较保守的估计（例如 VARCHAR（255） code>或 VARCHAR（50）），但您真的需要将这些字段分割成单独的表。
You should take a long hard look at your database schema and come up with something more reasonable - you could start with choosing some more conservative estimates on column sizes (like VARCHAR(255) or VARCHAR(50)), but you really need to split some of those fields out into separate tables.
这篇关于不能创建一个大小为8937的行，大于允许的最大值8060的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
