如何在Microsoft SQL Server中分块读取大型二进制文件？

针对你遇到的OPENROWSET(SINGLE_BLOB)全量加载大文件导致的性能问题，以下是两种无需存储数据到数据库的高效分块读取方案：

方案一：CLR用户定义函数（推荐，灵活可控） #

通过编写CLR函数直接在T-SQL中调用，实现指定字节范围的文件读取，避免全量加载。

步骤1：启用CLR集成 #

首先在SQL Server中启用CLR集成：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

步骤2：编写CLR函数（C#示例） #

创建一个类库项目，编写如下代码：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;

public class FileChunkReader
{
    [SqlFunction(DataAccess = DataAccessKind.None)]
    public static SqlBinary ReadFileChunk(SqlString filePath, SqlInt64 startOffset, SqlInt32 chunkSize)
    {
        if (filePath.IsNull || startOffset.IsNull || chunkSize.IsNull)
            return SqlBinary.Null;

        string path = filePath.Value;
        long offset = startOffset.Value;
        int size = chunkSize.Value;

        if (!File.Exists(path))
            throw new FileNotFoundException("指定文件不存在", path);
        if (offset < 0 || size <= 0)
            throw new ArgumentOutOfRangeException("起始偏移量需大于等于0，块大小需大于0");

        FileInfo fileInfo = new FileInfo(path);
        if (offset >= fileInfo.Length)
            return new SqlBinary(new byte[0]);

        int actualSize = (int)Math.Min(size, fileInfo.Length - offset);
        byte[] buffer = new byte[actualSize];
        
        using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read))
        {
            fs.Seek(offset, SeekOrigin.Begin);
            fs.Read(buffer, 0, actualSize);
        }

        return new SqlBinary(buffer);
    }
}

步骤3：部署CLR函数 #

编译生成DLL后，在SQL Server中创建程序集和函数：

CREATE ASSEMBLY FileChunkReaderAssembly
FROM 'C:\Path\To\Your\FileChunkReader.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS; -- 读取本地文件需该权限

CREATE FUNCTION dbo.ReadFileChunk(@filePath NVARCHAR(256), @startOffset BIGINT, @chunkSize INT)
RETURNS VARBINARY(MAX)
AS EXTERNAL NAME FileChunkReaderAssembly.FileChunkReader.ReadFileChunk;

步骤4：调用函数读取分块 #

直接调用函数读取指定范围的二进制数据（注意CLR偏移量从0开始，对应T-SQL SUBSTRING的起始值1）：

-- 读取文件第1-15字节（对应CLR偏移0，长度15）
SELECT dbo.ReadFileChunk('C:\bigfile', 0, 15);

该方式只会加载指定范围的内容，性能远优于全量读取后截取。

方案二：使用FORMAT FILE配合BULK INSERT（原生T-SQL，无需CLR） #

通过创建格式文件指定读取的字节范围，再用BULK INSERT将片段导入临时表，最后读取数据。

步骤1：创建格式文件（XML格式） #

创建FileChunkFormat.xml文件，修改OFFSET和LENGTH调整读取范围：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="BytesFixed" LENGTH="15" OFFSET="0"/> <!-- OFFSET为起始字节偏移（从0开始），LENGTH为读取长度 -->
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="ChunkData" xsi:type="SQLVARBINARY"/>
 </ROW>
</BCPFORMAT>

步骤2：导入并读取片段 #

CREATE TABLE #FileChunk (ChunkData VARBINARY(MAX));

BULK INSERT #FileChunk
FROM 'C:\bigfile'
WITH (
    FORMATFILE = 'C:\FileChunkFormat.xml',
    FIRSTROW = 1,
    LASTROW = 1
);

SELECT ChunkData FROM #FileChunk;

DROP TABLE #FileChunk;

此方式无需CLR，但需每次修改格式文件调整读取范围，灵活性稍弱。

内容的提问来源于stack exchange，提问作者Paul