如何使用非默认的列顺序构造Sql Server TVP

Question

我的场景很简单—我创建了一个TVP，其值为IEnumerable<SqlDataRecord>，并使用它执行查询。

我得到的结果没有任何意义。因此，我编写了一个TraceSql函数，它以一种可以复制并粘贴到SSMS中的方式转储SQL及其参数。

我很困惑地发现，我的函数显示的SQL在粘贴到SSMS并在那里运行时产生了正确的结果。

最后，我启动了Sql Server profiler，找到了神秘的根源。

下面是我的TraceSql函数生成的SQL:

DECLARE @AdmSiteId Int = 93
DECLARE @src dbo.TableOfObjectChecksum2
INSERT INTO @src (Id,Checksum,AuxId) VALUES
 (8395,258295360,1)
,(8395,1114574098,2)
,(8395,-19039848,3)
,(8395,337145572,4)
,(8395,1083939112,5)

SELECT ISNULL(src.Id, dst.ClientId) Id, ISNULL(src.AuxId, dst.ClientLegalId) AuxId,
  CASE
    WHEN src.Checksum IS NULL THEN 0
    WHEN dst.Checksum IS NOT NULL THEN 1
    ELSE 2
  END Checksum
FROM @src src
FULL JOIN AdmCustomerInfoLegal dst ON src.Id = dst.ClientId AND src.AuxId = dst.ClientLegalId
LEFT JOIN AdmClientSite cs ON cs.AdmClientMasterId = dst.ClientId
WHERE (cs.AdmSiteId = @AdmSiteId OR cs.AdmSiteId IS NULL) AND (src.Checksum IS NULL OR dst.Checksum IS NULL OR src.Checksum <> dst.Checksum)
ORDER BY Checksum, Id, AuxId

下面是分析器捕获的SQL:

declare @p4 dbo.TableOfObjectChecksum2
insert into @p4 values(8395,258295360,1)
insert into @p4 values(8395,1114574098,2)
insert into @p4 values(8395,-19039848,3)
insert into @p4 values(8395,337145572,4)
insert into @p4 values(8395,1083939112,5)
exec sp_executesql N'
SELECT ISNULL(src.Id, dst.ClientId) Id, ISNULL(src.AuxId, dst.ClientLegalId) AuxId, 
  CASE 
    WHEN src.Checksum IS NULL THEN 0
    WHEN dst.Checksum IS NOT NULL THEN 1
    ELSE 2 
  END Checksum
FROM @src src
FULL JOIN AdmCustomerInfoLegal dst ON src.Id = dst.ClientId AND src.AuxId = dst.ClientLegalId
LEFT JOIN AdmClientSite cs ON cs.AdmClientMasterId = dst.ClientId
WHERE (cs.AdmSiteId = @AdmSiteId OR cs.AdmSiteId IS NULL) AND (src.Checksum IS NULL OR dst.Checksum IS NULL OR src.Checksum <> dst.Checksum)
ORDER BY Checksum, Id, AuxId',N'@AdmSiteId int,@src [dbo].[TableOfObjectChecksum2] READONLY',@AdmSiteId=93,@src=@p4

注意，由分析器捕获的SQL中的INSERT语句没有指定列列表。但是，当我创建TVP时，我指定了不同的列顺序。

下面是dbo.TableOfObjectChecksum2表UDT的模式:

CREATE TYPE [dbo].[TableOfObjectChecksum2] AS TABLE(
    [Id] [int] NOT NULL,
    [AuxId] [int] NOT NULL,
    [Checksum] [int] NOT NULL,
    PRIMARY KEY ([Id],[AuxId])
)

下面是我如何在c#中创建各自的TVP:

var items = GetItemsSomehow();
var metadata = new[]
{
  new SqlMetaData("Id", SqlDbType.Int),
  new SqlMetaData("Checksum", SqlDbType.Int),
  new SqlMetaData("AuxId", SqlDbType.Int)
};
new SqlParameter("src", SqlDbType.Structured)
{
  TypeName = "dbo.TableOfObjectChecksum2",
  SqlValue = items.Select(x =>
  {
    var rec = new SqlDataRecord(metadata);
    rec.SetInt32(0, x.ClientId);
    rec.SetInt32(1, x.Checksum);
    rec.SetInt32(2, x.ClientLegalId);
    return rec;
  }),
};

我认为我在元数据数组中指定的列顺序定义了序数和字段之间的匹配。但它不是。

现在我明白了，最好的办法是改变我的TVP，以与数据库中定义的顺序相同的顺序传递列。但是，出于好奇，我如何创建具有不同列顺序的TVP，就像我的示例一样(这不起作用)?

编辑

我的英语显然不足以解释我自己。

请检查传递给SqlDataRecord构造函数的SqlMetadata[]参数。物品的顺序重要吗?我想是的。因此，如果我传入

new[]
{
  new SqlMetaData("Id", SqlDbType.Int),
  new SqlMetaData("Checksum", SqlDbType.Int),
  new SqlMetaData("AuxId", SqlDbType.Int)
};

则表示"在批处理插入SQL语句中指定顺序为Id,Checksum,AuxId的列"。换句话说，我认为批插入语句由ADO生成。. NET给出的上述元数据如下所示:

INSERT INTO WhatEver (Id,Checksum,AuxId) VALUES (...),(...),...,(...)

我认为假设这样的逻辑是合理的。在此假设下，代码

rec.SetInt32(0, x.ClientId);
rec.SetInt32(1, x.Checksum);
rec.SetInt32(2, x.ClientLegalId);

是绝对有效的，因为:

• 序0 is Id元数据-给定ClientId
• 序数1是校验和元数据-给定校验和
• 序数2是辅助元数据-给定ClientLegalId

然而，我偶然发现的是元数据项的顺序对ADO并不重要。NET内部，因为它实际生成的插入语句是这样的:

INSERT INTO WhatEver VALUES (...)
INSERT INTO WhatEver VALUES (...)
...
INSERT INTO WhatEver VALUES (...)

意思是，无论我如何排序元数据项，实际的插入SQL都会按照数据库模式中列的顺序执行。当然，代码

rec.SetInt32(0, x.ClientId);
rec.SetInt32(1, x.Checksum);
rec.SetInt32(2, x.ClientLegalId);

无效，因为在数据库中:

• 序数1对应于AuxId，现在它被赋予Checksum
• 序数2对应于Checksum，现在它被赋予ClientLegalId

所以，我的假设是错误的。

现在我的问题-是否仍然可以批量插入使用SqlDataRecord对象的集合，并有列的自定义顺序?还是只传递必需的列，依靠默认值和null来启动?因为没有给出显式列列表的插入SQL必须为每个列提供值，甚至是可空的和默认的。否则Sql Server吐出列名或提供的值的数量与表定义不匹配

Answer 1

我遇到了这个问题，不幸的是没有办法忽略列的顺序。我唯一能做的小改进是强类型:

 var record = new SqlDataRecord(
        new SqlMetaData(nameof(x.Id), SqlDbType.Int),
        new SqlMetaData(nameof(x.Checksum), SqlDbType.Int),
        new SqlMetaData(x.nameof(x.AuxId), SqlDbType.Int));
    
    var index = record.GetOrdinal(nameof(x.Id));
    record.SetInt32(index, x.Id);
    index = record.GetOrdinal(nameof(x.Checksum));
    record.SetInt32(index, x.Checksum);
    index = record.GetOrdinal(nameof(x.AuxId));
    record.SetInt32(index, x.AuxId);

仍然是顺序问题，列的名称和实体的属性名称应该匹配。

Answer 2

但是选择不匹配。
为什么名字不一致?

 rec.SetInt32(0, x.ClientId);
 rec.SetInt32(1, x.Checksum);
 rec.SetInt32(2, x.ClientLegalId);

CREATE TYPE [dbo].[TableOfObjectChecksum2] AS TABLE(
    [Id] [int] NOT NULL,
    [AuxId] [int] NOT NULL,
    [Checksum] [int] NOT NULL,
    PRIMARY KEY ([Id],[AuxId])
)