Tim Schmelter Tim Schmelter - 1 month ago 7x
C# Question

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

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]
FROM @VwdCodeList;";

as you can see i'm using
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();

for an

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]
SELECT @WatchListID, cl.VwdCode, cl.Sort
FROM @VwdCodeList cl;";

For the sake of completeness, here is the method that returns the
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;


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