Export (0) Print
Expand All

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

fOrderAsc

fOrderDesc

fUnique

Meaning

FALSE

FALSE

FALSE

Invalid flag state, rejected by server

FALSE

FALSE

TRUE

Column is in unique set

FALSE

TRUE

FALSE

Column is ordered descending

FALSE

TRUE

TRUE

Column is ordered descending and in unique set

TRUE

FALSE

FALSE

Column is ordered ascending

TRUE

FALSE

TRUE

Column is ordered ascending and in unique set

TRUE

TRUE

FALSE

Invalid flag state, rejected by server

TRUE

TRUE

TRUE

Invalid flag state, rejected by server

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 if a TVP is defined as:

create type myTvpe as table (f1 int, f2 varchar(max), f3 datetime)

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.

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

 
Show:
© 2014 Microsoft