Storage Requirements for Encryption

Use the ENCRYPT_AES or ENCRYPT_TDES built-in functions to encrypt data. Encrypted values of character data types are stored in BASE64 format (also called Radix-64). For character data, this requires significantly more storage than the corresponding unencrypted data. Omitting the hint can reduce encryption overhead by more than 50 bytes for each encrypted value. It is the responsibility of the user to make sufficient storage space available for encrypted values.

The following table lists the data types that can be encrypted, and built-in functions that you can use to encrypt and decrypt values of those data types:

If the encrypted VARCHAR (or NVARCHAR) value is longer than the 255 byte maximum size for those data types, the encryption function returns a CHAR (or NCHAR) value of sufficient size to store the encrypted value.

Original Data Type	Encrypted Data Type	BASE64 Format	Decryption Function
CHAR	CHAR	Yes	DECRYPT_CHAR
NCHAR	NCHAR	Yes	DECRYPT_CHAR
VARCHAR	VARCHAR	Yes	DECRYPT_CHAR
NVARCHAR	NVARCHAR	Yes	DECRYPT_CHAR
LVARCHAR	LVARCHAR	Yes	DECRYPT_CHAR
BLOB	BLOB	No	DECRYPT_BINARY
CLOB	BLOB	No	DECRYPT_CHAR

DECRYPT_BINARY and DECRYPT_CHAR both return the same value from encrypted CHAR, NCHAR, VARCHAR, NVARCHAR, or LVARCHAR values. No built-in encryption or decryption functions support BYTE or TEXT data types, but you can use BLOB data types to encrypt very large strings.

If the declared size of a database column in which you intend to store encrypted data is smaller than the encrypted data length, truncation occurs when you insert the encrypted data into the column. The truncated data cannot subsequently be decrypted, because the data length indicated in the header of the encrypted string does not match what the column stores.

To avoid truncation, make sure that any column that stored encrypted strings has sufficient length. (See the cross-reference in the next paragraph for details of how to calculate the length of an encrypted string.)

Besides the unencrypted data length, the storage required for encrypted data depends on the encoding format, on whether you specify a hint, and on the block size of the encryption function. For a formula to estimate the encrypted size, see "Calculating storage requirements for encrypted data" on page ***.