CLR表值函数如何“流化”？

Question

表值Sql Clr函数上的MSDN文档指出：

Transact-SQL表值函数实现了将该函数调用到中间表中的结果。…相比之下，CLR表值函数代表流式传输的替代方案。无需在单个表中实现整个结果集。由托管函数返回的IEnumerable对象由调用表值函数的查询的执行计划直接调用，并且结果以增量方式使用。…如果您返回的行数非常大，这也是一个更好的选择，因为不必将它们作为整体存储在内存中。

然后，我发现“填充行”方法中不允许数据访问。这意味着您仍然必须在init方法中进行所有数据访问并将其保存在内存中，等待调用“填充行”。我误会了吗？如果不将结果强行放入数组或列表中，则会收到错误消息：“ ExecuteReader需要打开且可用的Connection。连接的当前状态为关闭。”

代码示例：

[<SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "Example8Row")>]
static member InitExample8() : System.Collections.IEnumerable = 
   let c = cn() // opens a context connection
   // I'd like to avoid forcing enumeration here:
   let data = getData c |> Array.ofSeq
   data :> System.Collections.IEnumerable

static member Example8Row ((obj : Object),(ssn: SqlChars byref)) = 
   do ssn <- new SqlChars(new SqlString(obj :?> string))
   ()

我在这里处理几百万行。有什么办法可以偷懒地做到这一点吗？

Answer 1

我假设您使用的是SQL Server2008。正如Microsoft员工在此页上所提到的，2008要求用DataAccessKind标记方法。读取的频率比2005年要高得多。其中之一是当TVF参与事务时（当我进行测试时，情况似乎总是如此）。解决方案是enlist=false在连接字符串中指定，但不能与结合使用context connection=true。这意味着您的连接字符串必须采用典型的客户端格式：Data Source=.;Initial Catalog=MyDb;Integrated Security=sspi;Enlist=false并且您的程序集必须permission_set=external_access至少使用来创建。以下作品：

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

namespace SqlClrTest {
    public static class Test {
        [SqlFunction(
            DataAccess = DataAccessKind.Read,
            SystemDataAccess = SystemDataAccessKind.Read,
            TableDefinition = "RowNumber int",
            FillRowMethodName = "FillRow"
            )]
        public static IEnumerable MyTest(SqlInt32 databaseID) {
            using (var con = new SqlConnection("data source=.;initial catalog=TEST;integrated security=sspi;enlist=false")) {
                con.Open();
                using (var cmd = new SqlCommand("select top (100) RowNumber from SSP1 where DatabaseID = @DatabaseID", con)) {
                    cmd.Parameters.AddWithValue("@DatabaseID", databaseID.IsNull ? (object)DBNull.Value : databaseID.Value);
                    using (var reader = cmd.ExecuteReader()) {
                        while (reader.Read())
                            yield return reader.GetInt32(0);
                    }
                }
            }
        }
        public static void FillRow(object obj, out SqlInt32 rowNumber) {
            rowNumber = (int)obj;
        }
    }
}

这是F＃中的同一件事：

namespace SqlClrTest

module Test =

    open System
    open System.Data
    open System.Data.SqlClient
    open System.Data.SqlTypes
    open Microsoft.SqlServer.Server

    [<SqlFunction(
        DataAccess = DataAccessKind.Read,
        SystemDataAccess = SystemDataAccessKind.Read,
        TableDefinition = "RowNumber int",
        FillRowMethodName = "FillRow"
        )>]
    let MyTest (databaseID:SqlInt32) =
        seq {
            use con = new SqlConnection("data source=.;initial catalog=TEST;integrated security=sspi;enlist=false")
            con.Open()
            use cmd = new SqlCommand("select top (100) RowNumber from SSP1 where DatabaseID = @DatabaseID", con)
            cmd.Parameters.AddWithValue("@DatabaseID", if databaseID.IsNull then box DBNull.Value else box databaseID.Value) |> ignore
            use reader = cmd.ExecuteReader()
            while reader.Read() do
                yield reader.GetInt32(0)
        } :> System.Collections.IEnumerable

    let FillRow (obj:obj) (rowNumber:SqlInt32 byref) =
        rowNumber <- SqlInt32(unbox obj)