Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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]
   ([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;
    }
}
like image 978
Tim Schmelter Avatar asked Sep 13 '16 10:09

Tim Schmelter


People also ask

What is the default ordering of data using the ORDER BY clause how could it be changed?

In SQL ORDER BY clause, we need to define ascending or descending order in which result needs to be sorted. By default, SQL Server sorts out results using ORDER BY clause in ascending order. Specifying ASC in order by clause is optional.

What is a table valued parameter?

Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.


1 Answers

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

like image 185
Shnugo Avatar answered Sep 28 '22 13:09

Shnugo