预分配 varbinary(max) 而不实际向 SQL Server 发送空数据? [英] Pre-allocate varbinary(max) without actually sending null data to the SQL Server?

问题描述

我将数据存储在 varbinary(max) 列中，并且出于客户端性能原因，使用 SQL Server 2005 通过.WRITE()"函数进行分块写入.这很好用，但由于副作用，我希望避免在每次追加期间动态调整 varbinary 列的大小.

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.

我想做的是通过将 varbinary 列预先分配到我想要的大小来优化它.例如，如果我要将 2MB 放入列中，我想先分配"该列，然后使用偏移/长度参数 .WRITE 真实数据.

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.

SQL 中有什么可以帮助我的吗?显然我不想向 SQL 服务器发送空字节数组，因为这会部分破坏 .WRITE 优化的目的.

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.

推荐答案

如果您使用的是 (MAX) 数据类型，那么 8K 以上的任何内容都会进入行溢出存储，而不是页内存储.因此，您只需要放入足够的数据以使其达到该行的 8K，从而占用该行的页内分配，而其余部分则进入行溢出存储.还有一些此处.

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.

如果您想预先分配所有内容，包括行溢出数据，您可以使用类似于(示例为 10000 字节)的内容:

If you want to pre-allocate everything, including the row overflow data, you can use something akin to (example does 10000 bytes):

SELECT CONVERT([varbinary](MAX), REPLICATE(CONVERT(varchar(MAX), '0'), 10000))

这篇关于预分配 varbinary(max) 而不实际向 SQL Server 发送空数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！