Is the sort-order of table-valued-parameters guaranteed to remain the same?

Question

I need to know if i need to add a sort-column to my custom table-type which i could then use to sort or if i can trust that the order of parameters remains the same even without such a column.

This is my type:

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

and this is one of the sql where is use it:

/// <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;";

as you can see i'm using ROW_NUMBER to get the sort-column value.

Do i need to add also a sort-column to the table-type or is it guaranteed(documented) that it remains the same? It seems to work.

This is the ADO.NET code where i use it:

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

GetVwdCodeRecords returns IEnumerable<SqlDataRecord> for an IEnumerable<string>.

---

Thanks all. If a future reader is interested to know how i've guaranteed the sort-order. I've modifed the table-type as suggested by adding another column:

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

The insert-sql is even simpler because the sort-column is passed in and not calculated:

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

For the sake of completeness, here is the method that returns the IEnumerable<SqlDataRecord> used as value for the table-valued-parameter(omitted error-handling):

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;
    }
}

Answer 1

In general: There is no implicit sort order on any result set.

The only way to achieve a guaranteed sort order is an ORDER BY on the outer-most query.

I'm sure you knew this already...

There is one specialty with ROW_NUMBER() OVER(ORDER BY ...) Read "General Remarks". But this is dangerous.

• The sort-order is only sure, if you are using a unique sort criterium in ORDER BY. You are using SELECT 1, which will not guarantee any sort order. This might work hundreds of tests and suddenly breaks...
• Any later action can destroy this sort-order. Just imagin you have a working function and - some months later - you use this function in a complex query.

I use this for example to create XML with a sure order, because within XML there is an implicit order given by position...

Answer 2

Yes, you need to add a column. SQL is a set-based language and sets are inherently unordered (admittedly, there are more than a few cases where SQL is kind of leaky about this).

If you want to use ORDER BY and you want to guarantee the results, you need to ensure that it's based on enough expressions based on the data within the table such that it uniquely defines the ordering. Here, you're ordering by a constant (and the warnings you receive if you just try ORDER BY 1 here should be enough clue that its not going to work well) so there are no guarantees on what ordering is actually applied.

Answer 3

Same order is not guaranteed unless you do an explicit order by..

Below are some tests..

 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 

and execution plan shows..

So below piece of code..

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

may not get you same order every time,unless you mention explicit order by