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 5-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.
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 |
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 |
The timestamp datatype is not supported by Adaptive Server.
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.
