表值参数的排序顺序是否保证不变？

Question

我需要知道是否需要在我的自定义表类型中添加一个排序列，以便我可以使用它来进行排序，或者我可以相信参数的顺序即使没有这样的列也会保持不变。

这是我的类型：

CREATE TYPE [dbo].[VwdCodeList] AS TABLE(
    [VwdCode] [varchar](50) NOT NULL
)

这是其中一个使用它的SQL语句：

/// <summary>
///     Inserts all new WatchListCodes for a given watchlist
/// </summary>
public const string InsertWatchListCodes = @"
INSERT INTO [dbo].[WatchListCodes]
   ([WatchListID]
   ,[VwdCode]
   ,[Sort])
 SELECT @WatchListID, VwdCode, ROW_NUMBER()OVER(ORDER BY (SELECT 1)) 
 FROM @VwdCodeList;";

如您所见，我正在使用 ROW_NUMBER 来获取排序列的值。

我是否需要在表类型中添加一个排序列，或者有没有保证（记录）它仍然保持不变？看起来它可以工作。

这是我使用它的 ADO.NET 代码：

SqlParameter vwdCodeListParameter = insertWatchListCodeCommand.Parameters.Add("@VwdCodeList", SqlDbType.Structured);
vwdCodeListParameter.TypeName = "[dbo].[VwdCodeList]";
vwdCodeListParameter.Value = WatchListSql.GetVwdCodeRecords(newVwdCodes, true);
int inserted = insertWatchListCodeCommand.ExecuteNonQuery();

GetVwdCodeRecords会返回一个IEnumerable<SqlDataRecord>用于一个IEnumerable<string>。

---

谢谢大家。如果将来的读者想知道我是如何确保排序顺序的，我已经按照建议修改了表类型，添加了另一列：

CREATE TYPE [dbo].[VwdCodeList] AS TABLE(
    [VwdCode] [varchar](50) NOT NULL,
    [Sort] [smallint] NOT NULL
)

插入SQL语句更加简单，因为排序列被传递而不是计算：

插入SQL语句更加简单，因为排序列被传递而不是计算：

public const string InsertWatchListCodes = @"
INSERT INTO [dbo].[WatchListCodes]
   ([WatchListID]
   ,[VwdCode]
   ,[Sort])
 SELECT @WatchListID, cl.VwdCode, cl.Sort 
 FROM @VwdCodeList cl;";

为了完整起见，这里是返回IEnumerable<SqlDataRecord>作为表值参数的值的方法（省略错误处理）:

public static IEnumerable<SqlDataRecord> GetVwdCodeRecords(IEnumerable<string> vwdCodes, bool trimCode = true)
{
    short currentSort = 0;
    foreach (string vwdCode in vwdCodes)
    {
        var record = new SqlDataRecord(
            new SqlMetaData("VwdCode", SqlDbType.VarChar, 50), 
            new SqlMetaData("Sort", SqlDbType.SmallInt));
        record.SetString(0, trimCode ? vwdCode.Trim() : vwdCode);
        record.SetInt16(1, ++currentSort);

        yield return record;
    }
}

- Tim Schmelter

---

很有可能这取决于表类型定义。例如，在表类型上定义主键有很大的机会改变记录返回的顺序。我会采取更安全的方法，即在表类型中添加另一列，并在您的ADO.NET代码中设置它。 - Sergey Kalinichenko

Answer 1

总体来说：任何结果集都没有隐式的排序顺序。 唯一的方法是在最外层查询中使用ORDER BY来实现保证的排序顺序。

我相信你已经知道这个...

有一个特殊情况下可以使用ROW_NUMBER() OVER(ORDER BY ...)，请阅读“概述”。但这很危险。

• 如果您在ORDER BY中使用唯一的排序标准，则排序顺序才是确定的。您正在使用SELECT 1，它不会保证任何排序顺序。这可能在数百次测试中起作用，但突然间就会出错...
• 任何后续操作都可能破坏此排序顺序。想象一下，您有一个工作函数，几个月后在复杂查询中使用此函数。

例如，我使用此功能创建XML以确保顺序，因为在XML中位置会隐式给出顺序...

- Shnugo

Answer 2

是的，您需要添加一列。SQL是一种基于集合的语言，集合本质上是无序的(尽管在 SQL 中有很多情况会出现这种泄漏的情况)。

如果您想使用 ORDER BY 并且想要保证结果，您需要确保它基于足够的表内数据表达式进行排序，以使其唯一地定义排序方式。在此，您正在按常量排序(如果您只是尝试 ORDER BY 1 ，您收到的警告应该足以说明它不会很好地工作)，所以实际应用的排序顺序没有任何保证。

- Damien_The_Unbeliever

---

谢谢。但是，自定义表类型用于表值参数时，它们是否会被SQL Server以不同的方式处理呢？这样他们就可以确保插入顺序保持不变。这将非常有用。我知道通常情况下顺序不能保证，并且表没有“固有”顺序。但是作为参数具有非常短的生命周期的表类型可能会表现得不同。但我想与普通表没有区别。 - Tim Schmelter

@TimSchmelter - 在引入表类型时，每个经验丰富的SQL开发人员都习惯于使用没有固有顺序的表进行工作。那么，为这些新对象提供额外的保证或开发工作的正当理由是什么？如果它们确实具有这种令人惊讶的新行为，那么您期望在文档中突出显示这一特性。 - Damien_The_Unbeliever

Answer 3

如果没有显式地使用order by，那么就不能保证同样的顺序。

以下是一些测试...

 create type numbes as table
 (
 num int primary key
 )


 DECLARE @nums AS numbes;  

 insert into @nums
 select row_number() over(order by(select 1)) 
 from
 master.sys.objects


 select Top 100* from @nums 

执行计划显示...

所以，下面的代码：

ROW_NUMBER()OVER(ORDER BY (SELECT 1)) 
 FROM @VwdCodeList;";

除非您明确指定顺序，否则可能无法每次获得相同的顺序。

- TheGameiswar

---

谢谢。但是，一个用于表值参数的自定义表类型是否可能被 SQL Server 以不同的方式处理？这样，它们实际上可以保证插入顺序保持不变。这将非常有用。我知道通常不保证顺序，并且一个表没有“固有”的顺序。但作为参数具有非常短的生命周期的表类型可能会有所不同。在您的示例中，您是从普通表中选择的。 - Tim Schmelter

我也曾认为表值参数是只读的，但在没有进行排序之前，任何结果集都不能保证排序。你可能还想查看这篇文章。 - TheGameiswar

https://blogs.msdn.microsoft.com/conor_cunningham_msft/2008/08/27/no-seatbelt-expecting-order-without-order-by/ - TheGameiswar

@TimSchmelter，我们不知道...即使我们知道这个顺序，也不应该依赖它。使用过程化方法，您告诉引擎如何完成某事。但是对于基于集合的SQL Server，您只需告诉您想要什么。引擎可能会以一种或另一种方式解决它，这完全不应该困扰您...想象一下，如果您的方法适用于短列表，直到您提供一个非常大的列表并且引擎决定进入并行处理...如果您的列表很短，您可以将参数作为XML提交。这是隐式排序的... - Shnugo