Convert binary and varbinary data

data-types

#tsql#data-types

Analytics Platform System (PDW)

SQL analytics endpoint in

Microsoft Fabric

Warehouse in Microsoft Fabric

Binary data types of either fixed length or variable length.

Fixed-length binary data with a length of

bytes, where

is a value from 1 through 8,000. The

storage size is

bytes.

Variable-length binary data.

can be a value from 1 through 8,000.

indicates that the

maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered

2 bytes. The data that is entered can be 0 bytes in length. The ANSI SQL synonym for

is.

The default length is 1 when

isn’t specified in a data definition or variable declaration

statement. When

isn’t specified with the

function, the default length is 30.

the sizes of the column data entries are consistent.

the sizes of the column data entries vary considerably.

the column data entries exceed 8,000 bytes.

When converting data from a string data type to a

data type of unequal

length, SQL Server pads or truncates the data on the right. These string data types are:

Expand table

When other data types are converted to

, the data is padded or truncated

on the left. Padding is achieved by using hexadecimal zeros.

Converting data to the

and

data types is useful if

data is the easiest

way to move around data. At some point, you might convert a value type to a binary value of

large enough size and then convert it back. This conversion always results in the same value if

both conversions are taking place on the same version of SQL Server. The binary

representation of a value might change from version to version of SQL Server.

You can convert

, and

or. If you convert the

value back to an integer value, this value is different from the original integer value if

truncation occurred. For example, the following SELECT statement shows that the integer value

is stored as a binary

However, the following

statement shows that if the

target is too small to hold

the entire value, the leading digits are silently truncated so that the same number is stored as

The following batch shows that this silent truncation can affect arithmetic operations without

raising an error:

The final result is

, not.

CAST and CONVERT (Transact-SQL)

Data Type Conversion (Database Engine)

Data Types (Transact-SQL)

７

Note

Conversions between any data type and the

data types are not guaranteed to be

the same between versions of SQL Server.