How to Compute the Width of Encrypted Data
Encrypted data takes up more room than unencrypted data for the following reasons:
1. AES is a block cipher that operates only on 16-byte chunks, so padding is often necessary.
2. At-rest encrypted data includes a 4-byte hash to validate decryption processing.
3. Adding SALT to guarantee the unique encryption of each row of an encrypted column adds 16-bytes of overhead.
The HELP TABLE command displays the physical width of encrypted columns in an encryption section (see
Encryption Information Displayed with HELP TABLE) of the report.
Encryption adds about 25% to the data size.
Use the following algorithm to calculate the width of an encrypted column:
1. Start with the natural width of the column.
2. Add 1 for terminating null.
3. Round up to the nearest multiple of 16.
4. Add 4 for the salt length.
5. Multiply by 1.25.
The goal of encryption is to make meaningful data appear to be a random series of bits until the encryption algorithm in combination with the encryption key is used to restore the data to its original state. One result is that encrypted data does not compress well, so Vector does not compress encrypted columns.
Thus, the net effect on disk storage needs of encryption is a combination of the expansion of the encrypted rows for necessary overhead, and the loss of compressibility of the encrypted columns.
The AES_ENCRYPT_VARCHAR function accepts as input a string of type VARCHAR and encrypts the entire string. If you store AES_ENCRYPT_VARCHAR encrypted data in a database table, be sure to allocate sufficient space for the full encrypted data length. Truncated encrypted data cannot be decrypted successfully. For information on specifying error handling for string truncation, see String Truncation in the SQL Language Guide.