Length of encrypted columns

During create table, alter table, and select into operations, Adaptive Server calculates the maximum internal length of the encrypted column. To make decisions on schema arrangements and page sizes, the database owner must know the maximum length of the encrypted columns.

AES is a block-cipher algorithm. The length of encrypted data for block-cipher algorithms is a multiple of the block size of the encryption algorithm. For AES, the block size is 128 bits, or 16 bytes. Therefore, encrypted columns occupy a minimum of 16 bytes with additional space for:

The initialization vector. If used, the initialization vector adds 16 bytes to each encrypted column. By default, the encryption process uses an initialization vector. Specify init_vector null on create encryption key to omit the initialization vector.
The length of the plain text data. If the column type is char, varchar, binary, or varbinary, the data is prefixed with 2 bytes before encryption. These 2 bytes denote the length of the plain text data. No extra space is used by the encrypted column unless the additional 2 bytes result in the cipher text occupying an extra block.
A sentinel byte, which is a byte appended to the cipher text to safeguard against the database system trimming trailing zeros.

In Table 3-1, the lengths in the Maximum encrypted data length columns reflect the value in sycolumns.encrlen for a column of the specified type and length.

**Table 3-1: cipher text lengths**
User-specified column type	Input data length	Encrypted column type	Maximum encrypted data length (no init vector)	Actual encrypted data length (no init vector)	Maximum encrypted data length (with init vector)	Actual encrypted data length (with init vector)
bigint	8	varbinary	17	17	33	33
unsigned bigint	8	varbinary	17	17	33	33
tinyint, smallint, or int (signed or unsigned)	1, 2, or 4	varbinary	17	17	33	33
tinyint, smallint, or int (signed or unsigned)	0 (null)	varbinary	17	0	33	0
float, float(4), real	4	varbinary	17	17	33	33
float, float(4), real	0 (null)	varbinary	17	0	33	0
float(8), double	8	varbinary	17	17	33	33
float(8), double	0 (null)	varbinary	17	0	33	0
numeric(10,2)	3	varbinary	17	17	33	33
numeric (38,2)	18	varbinary	33	33	49	49
numeric (38,2)	0 (null)	varbinary	33	0	49	0
char, varchar (100)	1	varbinary	113	17	129	33
char, varchar(100)	14	varbinary	113	17	129	33
char, varchar(100)	15	varbinary	113	33	129	49
char, varchar(100)	31	varbinary	113	49	129	65
char, varchar(100)	0 (null)	varbinary	113	0	129	0
binary, varbinary(100)	1	varbinary	113	17	129	33
binary, varbinary(100)	14	varbinary	113	17	129	33
binary, varbinary(100)	15	varbinary	113	33	129	49
binary, varbinary(100)	31	varbinary	113	49	129	65
binary, varbinary(100)	0 (null)	varbinary	113	0	65	0
unichar(10)	2 (1 unichar character)	varbinary	33	17	49	33
unichar(10)	20 (10 unichar characters)	varbinary	33	33	49	49
univarchar(20)	20 (10 unichar characters)	varbinary	49	33	65	49

text, image, timestamp and unitext datatypes are not supported by Adaptive Server.

**Table 3-2: datatype length for encrypted columns**
Datatype	Input data length	Encrypted column type	Max encrypted data length (no init_vector)	Actual encrypted data length (no init vector)	Max encrypted data length with init_vector	Actual encrypted data length (with init_vector)
date	4	varbinary	17	17	33	33
time	4	varbinary	17	17	33	33
time	null	varbinary	17	0	33	0
smalldatetime	4	varbinary	17	17	33	33
datetime	8	varbinary	17	17	33	33
smallmoney	4	varbinary	17	17	33	33
money	8	varbinary	17	17	33	33
money	null	varbinary	17	0	33	0
bit	1	varbinary	17	17	33	33

char and binary are treated as variable-length datatypes and are stripped of blanks and zero padding before encryption. Any blank or zero padding is applied when the data is decrypted.

The column length on disk increases for encrypted columns, but the increases are invisible to tools and commands. For example, sp_help shows only the original size.