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) 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 ; 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 which will 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 typically 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.