2.2.5.4.3 Variable-Length Data Types

The data type token values defined in this section have a length value associated with the data type because the data values corresponding to these data types are represented by a variable number of bytes.

 GUIDTYPE            =   %x24  ; UniqueIdentifier
 INTNTYPE            =   %x26  ; (see below)
 BITNTYPE            =   %x68  ; (see below)
 DECIMALNTYPE        =   %x6A  ; Decimal
 NUMERICNTYPE        =   %x6C  ; Numeric
 FLTNTYPE            =   %x6D  ; (see below)
 MONEYNTYPE          =   %x6E  ; (see below)
 DATETIMNTYPE        =   %x6F  ; (see below)
 DATENTYPE           =   %x28  ; (introduced in TDS 7.3)
 TIMENTYPE           =   %x29  ; (introduced in TDS 7.3)
 DATETIME2NTYPE      =   %x2A  ; (introduced in TDS 7.3)
 DATETIMEOFFSETNTYPE =   %x2B  ; (introduced in TDS 7.3)
 CHARTYPE            =   %x2F  ; Char (legacy support)
 VARCHARTYPE         =   %x27  ; VarChar (legacy support)
 BINARYTYPE          =   %x2D  ; Binary (legacy support)
 VARBINARYTYPE       =   %x25  ; VarBinary (legacy support)
  
 BIGVARBINARYTYPE    =   %xA5  ; VarBinary
 BIGVARCHARTYPE      =   %xA7  ; VarChar
 BIGBINARYTYPE       =   %xAD  ; Binary
 BIGCHARTYPE         =   %xAF  ; Char
 NVARCHARTYPE        =   %xE7  ; NVarChar
 NCHARTYPE           =   %xEF  ; NChar
 XMLTYPE             =   %xF1  ; XML (introduced in TDS 7.2)
 UDTTYPE             =   %xF0  ; CLR UDT (introduced in TDS 7.2)
  
 TEXTTYPE            =   %x23  ; Text
 IMAGETYPE           =   %x22  ; Image
 NTEXTTYPE           =   %x63  ; NText
 SSVARIANTTYPE       =   %x62  ; sql_variant (introduced in TDS 7.2)
 JSONTYPE            =   %xF4
  
 BYTELEN_TYPE        =   GUIDTYPE
                         /
                         INTNTYPE
                         /
                         DECIMALTYPE
                         /
                         NUMERICTYPE
                         /
                         BITNTYPE
                         /
                         DECIMALNTYPE
                         /
                         NUMERICNTYPE
                         /
                         FLTNTYPE
                         /
                         MONEYNTYPE
                         /
                         DATETIMNTYPE
                         /
                         DATENTYPE
                         /
                         TIMENTYPE
                         /
                         DATETIME2NTYPE
                         /
                         DATETIMEOFFSETNTYPE
                         /
                         CHARTYPE
                         /
                         VARCHARTYPE
                         /
                         BINARYTYPE
                         /
                         VARBINARYTYPE  ; the length value associated
                                        ; with these data types is
                                        ; specified within a BYTE

For DECIMALNTYPE and NUMERICNTYPE, the only valid lengths are 0x05, 0x09, 0x0D, and 0x11 for non-NULL instances.

For MONEYNTYPE, the only valid lengths are 0x04 and 0x08, which map to smallmoney and money SQL data types respectively.

For DATETIMNTYPE, the only valid lengths are 0x04 and 0x08, which map to smalldatetime and datetime SQL data types respectively.

For INTNTYPE, the only valid lengths are 0x01, 0x02, 0x04, and 0x08, which map to tinyint, smallint, int, and bigint SQL data types respectively.

For FLTNTYPE, the only valid lengths are 0x04 and 0x08, which map to 7-digit precision float and 15-digit precision float SQL data types respectively.

For GUIDTYPE, the only valid lengths are 0x10 for non-null instances and 0x00 for NULL instances.

For BITNTYPE, the only valid lengths are 0x01 for non-null instances and 0x00 for NULL instances.

For DATENTYPE, the only valid lengths are 0x03 for non-NULL instances and 0x00 for NULL instances.

For TIMENTYPE, the only valid lengths (along with the associated scale value) are:

SCALE

1

2

3

4

5

6

7

LENGTH

0x03

0x03

0x04

0x04

0x05

0x05

0x05

For DATETIME2NTYPE, the only valid lengths (along with the associated scale value) are:

SCALE

1

2

3

4

5

6

7

LENGTH

0x06

0x06

0x07

0x07

0x08

0x08

0x08

For DATETIMEOFFSETNTYPE, the only valid lengths (along with the associated scale value) are:

SCALE

1

2

3

4

5

6

7

LENGTH

0x08

0x08

0x09

0x09

0x0A

0x0A

0x0A

Exceptions are thrown when invalid lengths are presented to the server during BulkLoadBCP and RPC requests.

For all variable length data types, the value is 0x00 for NULL instances.

 USHORTLEN_TYPE   =   BIGVARBINARYTYPE
                      /
                      BIGVARCHARTYPE
                      /
                      BIGBINARYTYPE
                      /
                      BIGCHARTYPE
                      /
                      NVARCHARTYPE
                      /
                      NCHARTYPE  ; the length value associated with
                                 ; these data types is specified
                                 ; within a USHORT
  
 LONGLEN_TYPE     =   IMAGETYPE
                      /
                      NTEXTTYPE
                      /
                      SSVARIANTTYPE
                      /
                      TEXTTYPE
                      /
                      XMLTYPE
                      /
                      JSONTYPE   ; the length value associated with
                                 ; these data types is specified
                                 ; within a LONG

Notes

  • MaxLength for an SSVARIANTTYPE is 8009 (8000 for strings). For more details, see section 2.2.5.5.4.

  • XMLTYPE is only a valid LONGLEN_TYPE for BulkLoadBCP.

MaxLength for an SSVARIANTTYPE is 8009 (string of 8000 bytes).

 VARLENTYPE       =   BYTELEN_TYPE
                      /
                      USHORTLEN_TYPE
                       /
                      LONGLEN_TYPE

Nullable values are returned by using the INTNTYPE, BITNTYPE, FLTNTYPE, GUIDTYPE, MONEYNTYPE, and DATETIMNTYPE tokens that use the length byte to specify the length of the value or GEN_NULL as appropriate.

There are two types of variable-length data types. These are real variable-length data types, like char and binary, and nullable data types, which have either a normal fixed length that corresponds to their type or to a special length if null.

Char and binary data types have values that either are null or are 0 to 65534 (0x0000 to 0xFFFE) bytes of data. Null is represented by a length of 65535 (0xFFFF). A non-nullable char or binary can still have a length of zero (for example, an empty value). A program that MUST pad a value to a fixed length adds blanks to the end of a char and adds binary zeros to the end of a binary.

Text and image data types have values that either are null or are 0 to 2 gigabytes (0x00000000 to 0x7FFFFFFF bytes) of data. Null is represented by a length of -1 (0xFFFFFFFF). No other length specification is supported.

Other nullable data types have a length of 0 when they are null.