预分配 varbinary(max) 而不实际将空数据发送到 SQL Server？
我将数据存储在 varbinary(max) 列中，出于客户端性能原因，使用 SQL Server 2005 通过“.WRITE()”函数进行分块写入。这很好用，但是由于副作用，我想避免varbinary 列在每次追加期间动态调整大小。
我想做的是通过将 varbinary 列预先分配到我想要的大小来优化它。例如，如果我要将 2MB 放入列中，我想首先“分配”该列，然后使用偏移/长度参数写入实际数据。
SQL 中有什么可以帮助我的吗？显然我不想将空字节数组发送到 SQL 服务器，因为这会部分破坏 .WRITE 优化的目的。
I'm storing data in a varbinary(max) column and, for client performance reasons, chunking writes through the ".WRITE()" function using SQL Server 2005. This works great but, due to the side effects, I want to avoid the varbinary column dynamically sizing during each append.
What I'd like to do is optimize this by pre-allocating the varbinary column to the size I want. For example if I'm going to drop 2MB into the column I would like to 'allocate' the column first, then .WRITE the real data using offset/length parameters.
Is there anything in SQL that can help me here? Obviously I don't want to send a null byte array to the SQL server, as this would partially defeat the purpose of the .WRITE optimization.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您使用 (MAX) 数据类型，则任何超过 8K 的数据都会进入行溢出存储，而不是页内存储。因此，您只需要输入足够的数据以使其达到该行的 8K，从而占用该行的页内分配，其余的数据无论如何都会进入行溢出存储。 此处还有更多内容。
如果您想预分配所有内容，包括行溢出数据，您可以使用类似的东西（示例为 10000 字节）：
If you're using a (MAX) data type, then anything above 8K goes into row overflow storage, not the in-page storage. So you just need to put in enough data to get it up to the 8K for the row, making that take up the in-page allocation for the row, and the rest goes into row-overflow storage anyway. There's some more here.
If you want to pre-allocate everything, including the row overflow data, you can use something akin to (example does 10000 bytes):
首先感谢所提供的答案 - 这是一个很大的帮助！但是，您可能需要考虑一个细微的变化。上面的代码实际上使用转换后的零字符（十六进制代码 0x30）分配 varbinary 字段。这可能不是您真正想要的，特别是如果您想稍后在该字段上执行二进制操作。我认为更有用的是用 NUL 值（十六进制代码 0x00）分配该字段，以便默认情况下关闭所有位。为此，只需进行以下更正：
SELECT CONVERT([varbinary](MAX), REPLICATE(CONVERT(varchar(MAX), CHAR(0)), 10000))First of all kudos to the answer provided - this was a great help! However, there is one slight change that you may want to consider. The code above actually allocates the varbinary field with a converted zero character (hex code 0x30). This may not be what you actually want, particularly if you want to perform binary operations on the field later. What I think is more useful is to allocate the field with a NUL value (hex code 0x00) so that all the bits are turned off by default. To do this, simply make the following correction:
SELECT CONVERT([varbinary](MAX), REPLICATE(CONVERT(varchar(MAX), CHAR(0)), 10000))