HASHBYTES (Transact-SQL)HASHBYTES (Transact-SQL)

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

傳回其在 [SQL Server]SQL Server 中輸入的 MD2、MD4、MD5、SHA、SHA1 或 SHA2 雜湊。Returns the MD2, MD4, MD5, SHA, SHA1, or SHA2 hash of its input in [SQL Server]SQL Server.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

HASHBYTES ( '<algorithm>', { @input | 'input' } )  
  
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512   

引數Arguments

'<algorithm>''<algorithm>'
識別用來雜湊輸入的雜湊演算法。Identifies the hashing algorithm to be used to hash the input. 這是必要的引數,沒有預設值。This is a required argument with no default. 必須加上單引號。The single quotation marks are required. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,取代 SHA2_256 和 SHA2_512 以外的所有演算法。Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x), all algorithms other than SHA2_256, and SHA2_512 are deprecated.

@input**
指定含有要雜湊之資料的變數。Specifies a variable containing the data to be hashed.
@input** 為 varcharnvarchar,或 varbinary**@input** is varchar, nvarchar, or varbinary.

' input '' input '
指定運算式,這個運算式評估為要雜湊的字元或二進位字串。Specifies an expression that evaluates to a character or binary string to be hashed.

輸出符合演算法標準:適用於 MD2、MD4 和 MD5 的 128 位元 (16 位元組);適用於 SHA 和 SHA1 的 160 位元 (20 位元組);適用於 SHA2_256 的 256 位元 (32 位元組),以及適用於 SHA2_512 的 512 位元 (64 位元組)。The output conforms to the algorithm standard: 128 bits (16 bytes) for MD2, MD4, and MD5; 160 bits (20 bytes) for SHA and SHA1; 256 bits (32 bytes) for SHA2_256, and 512 bits (64 bytes) for SHA2_512.

適用於SQL Server 2012 (11.x)SQL Server 2012 (11.x)Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017

針對 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 及更早版本,允許的輸入值限制為 8000 個位元組。For SQL Server 2014 (12.x)SQL Server 2014 (12.x) and earlier, allowed input values are limited to 8000 bytes.

傳回值Return Value

varbinary (最大 8000 位元組)varbinary (maximum 8000 bytes)

RemarksRemarks

請考慮使用 CHECKSUMBINARY_CHECKSUM 作為計算雜湊值的替代方案。Consider using CHECKSUM or BINARY_CHECKSUM as alternatives to compute a hash value.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始已淘汰 MD2、MD4、MD5、SHA 和 SHA1。The MD2, MD4, MD5, SHA, and SHA1 algorithms are deprecated starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x). 請改用 SHA2_256 或 SHA2_512。Use SHA2_256 or SHA2_512 instead. 較舊的演算法會繼續運作,但它們會引發淘汰事件。Older algorithms will continue working, but they will raise a deprecation event.

範例Examples

傳回變數的雜湊Return the hash of a variable

下列範例會傳回儲存於 @HashThis 變數中 nvarchar 資料的 SHA1 雜湊。The following example returns the SHA1 hash of the nvarchar data stored in variable @HashThis.

DECLARE @HashThis nvarchar(4000);  
SET @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');  
SELECT HASHBYTES('SHA1', @HashThis);  

傳回資料表資料行的雜湊Return the hash of a table column

下列範例會傳回 c1 資料表 Test1 資料行中之值的 SHA1 雜湊。The following example returns the SHA1 hash of the values in column c1 in the table Test1.

CREATE TABLE dbo.Test1 (c1 nvarchar(50));  
INSERT dbo.Test1 VALUES ('This is a test.');  
INSERT dbo.Test1 VALUES ('This is test 2.');  
SELECT HASHBYTES('SHA1', c1) FROM dbo.Test1;  

以下為結果集:Here is the result set.

-------------------------------------------  
0x0E7AAB0B4FF0FD2DFB4F0233E2EE7A26CD08F173  
0xF643A82F948DEFB922B12E50B950CEE130A934D6  
  
(2 row(s) affected)  

另請參閱See Also

選擇加密演算法Choose an Encryption Algorithm
CHECKSUM_AGG (Transact-SQL)CHECKSUM_AGG (Transact-SQL)
CHECKSUM (Transact-SQL)CHECKSUM (Transact-SQL)
BINARY_CHECKSUM (Transact-SQL)BINARY_CHECKSUM (Transact-SQL)