2.2.5.5.5.2 Optional Metadata Tokens

TVP_ORDER_UNIQUE definition

 TVP_ORDER_UNIQUE_TOKEN =   %x10
 Count                  =   USHORT ; Count of ColNums to follow
 ColNum                 =   USHORT ; A single-column ordinal
 fOrderAsc              =   BIT    ; Column-ordered ascending – %x01
 fOrderDesc             =   BIT    ; Column-ordered descending – %x02
 fUnique                =   BIT    ; Column is in unique set – %x04
 Reserved1              =   5BIT   ; Five reserved bits
  
 OrderUniqueFlags       =   fOrderAsc
                            fOrderDesc
                            fUnique
                            Reserved1
  
 TVP_ORDER_UNIQUE       =   TVP_ORDER_UNIQUE_TOKEN
                            ( Count (<Count> (ColNum OrderUniqueFlags) ) )

TVP_ORDER_UNIQUE is similar to the ORDER token that is currently used in TDS responses from the server.

TVP_ORDER_UNIQUE is optional.

ColNum ordinals are 1..N, where 1 is the first column in TVP_COLMETADATA. That is, ordinals start with 1.

Each TVP_ORDER_UNIQUE token can describe a set of columns for ordering and/or a set of columns for uniqueness.

The first column ordinal with an ordering bit set is the primary sort column, the second column ordinal with an ordering bit set is the secondary sort column, and so on.

The client can send 0 or 1 TVP_ORDER_UNIQUE tokens in a single TVP.

The TVP_ORDER_UNIQUE token MUST always be sent after TVP_COLMETADATA and before the first TVP_ROW token.

When a TVP is sent to the server, each ColNum ordinal inside a TVP_ORDER_UNIQUE token MUST refer to a client generated column. Ordinals that refer to columns with fDefault set will be rejected by the server.

OrderUniqueFlags Possible Combinations And Meaning

TVP_COLUMN_ORDERING

TVP_COLUMN_ORDERING is an optional TVP metadata token that is used to allow the TDS client to send a different ordering of the columns in a TVP from the default ordering.

ColNum ordinals are 1..N, where 1 is first column in the TVP (ordinals start with 1, in other words). These are the same ordinals used with the TDS ORDER token, for example, to refer to column ordinal as the columns appear in left to right order.

 TVP_COLUMN_ORDERING_TOKEN =   %x11
 Count                     =   USHORT ; Count of ColNums to follow
 ColNum                    =   USHORT ; A single-column ordinal
  
 TVP_COLUMN_ORDERING       =   TVP_COLUMN_ORDERING_TOKEN
                               ( Count (<Count> ColNum) )

The client can send 0 or 1 TVP_COLUMN_ORDERING tokens in a single TVP.

The TVP_COLUMN_ORDERING token MUST always be sent after TVP_COLMETADATA and before the first TVP_ROW token.

Additional details about TVP_COLUMN_ORDERING

TVP_COLUMN_ORDERING is used to re-order the columns in a TVP. For example, say, a TVP is defined as the following:

 TVP_COLUMN_ORDERING = create type myTvpe as table (f1 int / f2 varchar (max) / f3 datetime)

Then, the TDS client might want to send the f2 field last inside the TVP as an optimization (streaming the large value last). So the client can send TVP_COLUMN_ORDERING with order 1,3,2 to indicate that inside the TVP_ROW section the column f1 is sent first, f3 is sent second, and f2 is sent third.

In this case, the TVP_COLUMN_ORDERING token on the wire for this example would be:

 11    ; TVP_COLUMN_ORDERING_TOKEN
 03 00 ; Count  - Number of ColNums to follow.
 01 00 ; ColNum - TVP column ordinal 1 is sent first in TVP_COLMETADATA.
 03 00 ; ColNum - TVP column ordinal 3 is sent second in TVP_COLMETADATA.
 02 00 ; ColNum - TVP column ordinal 2 is sent third in TVP_COLMETADATA.

Duplicate ColNum values are considered an error condition. The ordinal values of the columns in the actual TVP type are ordered starting with 1 for the first column and adding one for each column from left to right. The client MUST send one ColNum for each column described in the TVP_COLMETADATA (so Count MUST match number of columns in TVP_COLMETADATA).

TVP_ROW definition

 TVP_ROW_TOKEN  =   %x01           ;  A row as defined by TVP_COLMETADATA follows
 TvpColumnData  =   TYPE_VARBYTE   ;  Actual value must match metadata for the column
 AllColumnData  =   *TvpColumnData ;  Chunks of data, one per non-default column defined
                                   ;  in TVP_COLMETADATA
 TVP_ROW        =   TVP_ROW_TOKEN
                    AllColumnData
 TVP_END_TOKEN  =   %x00           ;  Terminator tag for TVP type, meaning
                                   ;  no more TVP_ROWs to follow and end of
                                   ;  successful transmission of a single TVP

TvpColumnData is repeated once for each non-default column of data defined in TVP_COLMETADATA.

Each row will contain one data "cell" per column specified in TVP_COLMETADATA. On input, columns with the fDefault flag set in TVP_COLMETADATA will be skipped to avoid sending redundant data.

Column data is ordered in same order as the order of items defined in TVP_COLMETADATA unless a TVP_COLUMN_ORDERING token has been sent to indicate a change in the ordering of the row values.