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:

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

Notetext, 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.

NoteThe 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.