binary 和 varbinary (Transact-SQL)binary and varbinary (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

固定長度或可變長度的二進位資料類型。Binary data types of either fixed length or variable length.


binary [ ( n ) ] 固定長度的二進位資料,其長度為 n 位元組,其中 n 是 1 到 8,000 的值。binary [ ( n ) ] Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. 儲存體大小是 n 位元組。The storage size is n bytes.

varbinary [ ( n | max) ] 可變長度的二進位資料。varbinary [ ( n | max) ] Variable-length binary data. n 可以是從 1 到 8,000 之間的值。n can be a value from 1 through 8,000. max 表示儲存體大小上限是 2^31-1 個位元組。max indicates that the maximum storage size is 2^31-1 bytes. 儲存體大小是輸入資料的實際長度再加上 2 位元組。The storage size is the actual length of the data entered + 2 bytes. 輸入的資料有可能是 0 位元組。The data that is entered can be 0 bytes in length. varbinary 的 ANSI SQL 同義字是 binary varyingThe ANSI SQL synonym for varbinary is binary varying.


當資料定義或變數宣告陳述式中未指定 n 時,預設長度為 1。The default length is 1 when n isn't specified in a data definition or variable declaration statement. n 不是由 CAST 函式指定時,預設長度為 30。When n isn't specified with the CAST function, the default length is 30.

資料類型Data type 使用時機...Use when ...
binarybinary 當資料行資料項目的大小一致時。the sizes of the column data entries are consistent.
varbinaryvarbinary 當資料行資料項目的大小變化相當大時。the sizes of the column data entries vary considerably.
varbinary(max)varbinary(max) 當資料行資料項目超過 8,000 位元組時。the column data entries exceed 8,000 bytes.

轉換 binary 與 varbinary 資料Converting binary and varbinary data

將資料從字串資料類型轉換成不等長的 binaryvarbinary 資料類型時,SQL ServerSQL Server 會填補或截斷右邊的資料。When converting data from a string data type to a binary or varbinary data type of unequal length, SQL ServerSQL Server pads or truncates the data on the right. 這些字串資料類型為:These string data types are:

  • charchar
  • varcharvarchar
  • ncharnchar
  • nvarcharnvarchar
  • binarybinary
  • varbinaryvarbinary
  • texttext
  • ntextntext
  • imageimage

將其他資料類型轉換成 binaryvarbinary 時,則在左側填補或截斷資料。When other data types are converted to binary or varbinary, the data is padded or truncated on the left. 使用十六進位零進行填補。Padding is achieved by using hexadecimal zeros.

binary 資料是搬移資料最簡易的方式,則將資料轉換成 binaryvarbinary 資料類型會非常有幫助。Converting data to the binary and varbinary data types is useful if binary 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. 如果這兩項轉換都在同一版的 SQL ServerSQL Server 上執行,這項轉換一律會產生相同的值。This conversion always results in the same value if both conversions are taking place on the same version of SQL ServerSQL Server. 數值的二進位表示法可能會隨著不同的 SQL ServerSQL Server 版本而變更。The binary representation of a value might change from version to version of SQL ServerSQL Server.

您可以將 intsmallinttinyint 轉換成 binaryvarbinaryYou can convert int, smallint, and tinyint to binary or varbinary. 如果您將 binary 值轉換回整數值,若有發生截斷,則此值會與原始的整數值不同。If you convert the binary value back to an integer value, this value will be different from the original integer value if truncation has occurred. 例如,下列 SELECT 陳述式所顯示的整數值 123456,會儲存為二進位的 0x0001e240For example, the following SELECT statement shows that the integer value 123456 is stored as a binary 0x0001e240:

SELECT CAST( 123456 AS BINARY(4) );  

但是,下列 SELECT 陳述式顯示如果 binary 目標太小,放不下整個值,就會自動將前面的位數截斷,所以同一個數字會儲存為 0xe240However, the following SELECT statement shows that if the binary target is too small to hold the entire value, the leading digits are silently truncated so that the same number is stored as 0xe240:

SELECT CAST( 123456 AS BINARY(2) );  

下列批次顯示悄悄截斷可能在不產生錯誤的情況下影響算術運算:The following batch shows that this silent truncation can affect arithmetic operations without raising an error:

DECLARE @BinaryVariable2 BINARY(2);  
SET @BinaryVariable2 = 123456;  
SET @BinaryVariable2 = @BinaryVariable2 + 1;  
SELECT CAST( @BinaryVariable2 AS INT);  

最後的結果是 57921,不是 123457The final result is 57921, not 123457.


於不同 SQL ServerSQL Server 版本中在任何資料類型與 binary 資料類型之間轉換,不保證結果都會一樣。Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL ServerSQL Server.

另請參閱See also

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
資料類型轉換 (資料庫引擎)Data Type Conversion (Database Engine)
資料類型 (Transact-SQL)Data Types (Transact-SQL)