Is the sort order of table parameters guaranteed unchanged?

Question

I need to know if I need to add a sort column to my own table type, which I could use to sort, or if I can trust that the order of the parameters remains unchanged even without such a column.

This is my type:

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

and this is one from sql where it is used:

 /// <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 use ROW_NUMBER to get the sort-column value.

Do I also need to add the sort column to the table type or ensure (documented) that it remains the same? This 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 an IEnumerable<SqlDataRecord> for an IEnumerable<string> .

---

Thanks to everyone. If the prospective reader is interested to know how I guaranteed the sort order. I changed the table type as suggested by adding another column:

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

Insert-sql is even simpler because sort-in columns are passed and not evaluated:

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

For completeness, here is a method that returns the value IEnumerable<SqlDataRecord> , used as the value for the table-valued parameter parameter (excluded 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 for any set of results .

only to achieve a guaranteed ORDER BY sort order for an external query .

I'm sure you already knew that ...

There is one specialty with ROW_NUMBER() OVER(ORDER BY ...) Reading General Notes . But it is dangerous.

• The sort order is only certain if you use the unique sort criteria in ORDER BY . You are using SELECT 1 , which does not guarantee the sort order. It can work hundreds of tests and suddenly breaks ...
• Any subsequent action may destroy this sort order. Just imagine that you have a working function, and after a few months you use this function in a complex query.

I use this, for example, to create XML with a confident order, because in XML there is an implicit order specified by position ...

Answer 2

Yes, you need to add a column. SQL is a set-based language, and sets are inherently disordered (admittedly, there are more than a few cases where SQL is insightful in this matter).

If you want to use ORDER BY and want to guarantee results, you need to make sure that it is based on a sufficient number of expressions based on the data in the table, so that it uniquely determines the order. This is where you order the constant (and the warnings you get if you are just trying to use ORDER BY 1 , there should be enough information that it will not work well), so there is no guarantee what ordering is actually applied.

Answer 3

The same order is not guaranteed unless you follow an explicit order ..

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 shows the execution plan.

So below is a code snippet.

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

may not get your same order every time unless you specify an explicit order