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...