title: Is the sort-order of table-valued-parameters guaranteed to remain the same?
body: <p>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.</p>
  
  <p>This is my type:</p>
  
  <pre><code>CREATE TYPE [dbo].[VwdCodeList] AS TABLE(
      [VwdCode] [varchar](50) NOT NULL
  )
  </code></pre>
  
  <p>and this is one of the sql where is use it:</p>
  
  <pre><code>/// &lt;summary&gt;
  ///     Inserts all new WatchListCodes for a given watchlist
  /// &lt;/summary&gt;
  public const string InsertWatchListCodes = @"
  INSERT INTO [dbo].[WatchListCodes]
     ([WatchListID]
     ,[VwdCode]
     ,[Sort])
   SELECT @WatchListID, VwdCode, ROW_NUMBER()OVER(ORDER BY (SELECT 1)) 
   FROM @VwdCodeList;";
  </code></pre>
  
  <p>as you can see i'm using <code>ROW_NUMBER</code> to get the sort-column value. </p>
  
  <p>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.</p>
  
  <p>This is the ADO.NET code where  i use it:</p>
  
  <pre><code>SqlParameter vwdCodeListParameter = insertWatchListCodeCommand.Parameters.Add("@VwdCodeList", SqlDbType.Structured);
  vwdCodeListParameter.TypeName = "[dbo].[VwdCodeList]";
  vwdCodeListParameter.Value = WatchListSql.GetVwdCodeRecords(newVwdCodes, true);
  int inserted = insertWatchListCodeCommand.ExecuteNonQuery();
  </code></pre>
  
  <p><code>GetVwdCodeRecords</code> returns <code>IEnumerable&lt;SqlDataRecord&gt;</code> for an <code>IEnumerable&lt;string&gt;</code>.</p>
  
  <hr>
  
  <p>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:</p>
  
  <pre><code>CREATE TYPE [dbo].[VwdCodeList] AS TABLE(
      [VwdCode] [varchar](50) NOT NULL,
      [Sort] [smallint] NOT NULL
  )
  </code></pre>
  
  <p>The insert-sql is even simpler because the sort-column is passed in and not calculated:</p>
  
  <pre><code>public const string InsertWatchListCodes = @"
  INSERT INTO [dbo].[WatchListCodes]
     ([WatchListID]
     ,[VwdCode]
     ,[Sort])
   SELECT @WatchListID, cl.VwdCode, cl.Sort 
   FROM @VwdCodeList cl;";
  </code></pre>
  
  <p>For the sake of completeness, here is the method that returns the <code>IEnumerable&lt;SqlDataRecord&gt;</code> used as value for the table-valued-parameter(omitted error-handling):</p>
  
  <pre><code>public static IEnumerable&lt;SqlDataRecord&gt; GetVwdCodeRecords(IEnumerable&lt;string&gt; 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;
      }
  }
  </code></pre>