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 plaintext 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 plaintext data. No extra space is used by the encrypted column unless the additional 2 bytes result in the ciphertext occupying an extra block.
A sentinel byte, which is a byte appended to the ciphertext to safeguard against the database system trimming trailing zeros.
User-specified column type |
Input data length |
Init vector? |
Internal column type |
Encrypted data length |
|---|---|---|---|---|
tinyint, smallint, or int (signed or unsigned) |
1, 2, or 4 |
No |
varbinary(17) |
17 |
tinyint, smallint, or int (signed or unsigned) |
1, 2, or 4 |
Yes |
varbinary(33) |
33 |
tinyint, smallint, or int (signed or unsigned) |
0 (null) |
No |
varbinary(17) |
0 |
float, float(4), real |
4 |
No |
varbinary(17) |
17 |
float, float(4), real |
4 |
Yes |
varbinary(33) |
33 |
float, float(4), real |
0 (null) |
No |
varbinary(17) |
0 |
float(8), double |
8 |
No |
varbinary(17) |
17 |
float(8), double |
8 |
Yes |
varbinary(33) |
33 |
float(8), double |
0 (null) |
No |
varbinary(17) |
0 |
numeric(10,2) |
3 |
No |
varbinary(17) |
17 |
numeric (10,2) |
3 |
Yes |
varbinary(33) |
33 |
numeric (38,2) |
18 |
No |
varbinary(33) |
33 |
numeric (38,2) |
18 |
Yes |
varbinary(49) |
49 |
numeric (38,2) |
0 (null) |
No |
varbinary(33) |
0 |
char, varchar (100) |
1 |
No |
varbinary(113) |
17 |
char, varchar(100) |
14 |
No |
varbinary(113) |
17 |
char, varchar(100) |
15 |
No |
varbinary(113) |
33 |
char, varchar(100) |
15 |
Yes |
varbinary(117) |
49 |
char, varchar(100) |
31 |
Yes |
varbinary(117) |
65 |
char, varchar(100) |
0 (null) |
Yes |
varbinary(117) |
0 |
binary, varbinary(100) |
1 |
No |
varbinary(113) |
17 |
binary, varbinary(100) |
14 |
No |
varbinary(113) |
17 |
binary, varbinary(100) |
15 |
No |
varbinary(113) |
33 |
binary, varbinary(100) |
15 |
Yes |
varbinary(117) |
49 |
binary, varbinary(100) |
31 |
Yes |
varbinary(117) |
65 |
binary, varbinary(100) |
0 (null) |
Yes |
varbinary(117) |
0 |
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.
