hashbytes

Produces a fixed-length, hash value expression.

Syntax

hashbytes(algorithm, expression[, expression...] [, using options])

Parameters

expression[, expression...] – 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.
algorithm – 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”.
- 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:
  - lsb – all byte-order dependent data is normalized to little-endian byte-order before being hashed.
  - msb – all byte-order dependent data is normalized to big-endian byte-order before being hashed.
  - unicode – character data is normalized to unicode (UTF–16) before being hashed.
    
    Note: A UTF – 16 string is similar to an array of short integers. Because it is byte-order dependent, use lsb or msb in conjunction with UNICODE for platform independence.
  - unicode_lsb – a combination of unicode and lsb.
  - unicode_msb – a combination of unicode and msb.

Examples

Example 1 – 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

Example 2 – Shows how col1, col2, and col3 are used to partition rows into five partitions.
```
alter table xtable partition by hash(col1, col2, col3) 5
```

Usage

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” produce different hash values.

Note: Trailing null values are trimmed by the SAP ASE 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.

Note: 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.

Standards

SQL92- and SQL99-compliant

Permissions

Any user can execute hashbyte.