Produces a fixed-length, hash value expression.
hashbytes(algorithm, expression[, expression...] [, using options])
– is the value to be hashed. This value can be a column name, variable, constant expression, or a combination of these that produces a single value. It cannot be image, text, unitext, or off-row Java datatypes.
is the algorithm used to produce the hash value. A character literal (not a variable or a column name) that can take the values “md5”, “sha”, “sha1”, or “ptn”.
Algorithm |
Description |
|---|---|
Md5 |
Message Digest Algorithm 5 – is the cryptographic hash algorithm with a 128 bit hash value. hashbytes('md5', expression[,...]) results in a varbinary 16-byte value. |
Sha-Sha1 |
Secure Hash Algorithm – is the cryptographic hash algorithm with a 160-bit hash value. hashbytes('shal', expression[,...]) results in a varbinary 20-byte value. |
Ptn |
The partition hash algorithm with 32-bit hash value. The using clause is ignored for the ‘ptn’ algorithm. hashbytes('ptn', expression[,...]) results in an unsigned int 4-byte value. |
using |
Orders bytes for platform independence. The optional using clause can precede the following option strings:
|
Seals each row of a table against tampering. This example assumes the existence of a user table called “xtable” and col1, col2, col3 and tamper_seal.
update xtable set tamper_seal=hashbytes('sha1', col1,
col2, col4, @salt)
--
declare @nparts unsigned int
select @nparts= 5
select hashbytes('ptn', col1, col2, col3) % nparts from xtable
Shows how col1, col2, and col3 will be used to partition rows into five partitions.
alter table xtable partition by hash(col1, col2, col3) 5
The algorithm parameter is not case-sensitive; “md5,” “Md5” and “MD5” are all equivalent. However, if the expression is specified as a character datatype, the value is case sensitive. “Time,” “TIME,” and “time” will produce different hash values.
Trailing null values are trimmed by Adaptive Server
when inserting into varbinary columns.
In the absence of a using clause, the bytes that form expression are fed into the hash algorithm in the order they appear in memory. For many datatypes, order is significant. For example, the binary representation of the 4-byte INT value 1 will be 0x00, 0x00, 0x00, 0x01, on MSB-first (big-endian) platforms and 0x01, 0x00, 0x00, 0x00 on LSB-first (little-endian) platforms. Because the stream of bytes is different for different platforms, the hash value is different as well.
With the using clause, the bytes that form expression can be fed into the hashing algorithm in a platform-independent manner. The using clause can also be used to transform character data into Unicode so that the hash value becomes independent of the server’s character configuration.
The hash algorithms MD5 and SHA1 are
no longer considered entirely secure by the cryptographic community.
Be aware of the risks of using MD5 or SHA1 in
a security-critical context.
SQL92- and SQL99-compliant
Any user can execute hashbyte.
See also hash for platform dependent hash values.
