Estimating storage requirements for encrypted columns–AE learning series part 04

  • Article

As we discussed in earlier posts and as you might already be aware, AlwaysEncrypted columns require additional storage after they’re encrypted. Of course, that means that you need to plan properly as this might cause effects to your database, such as data file growth, fragmentation, etc.

So, how exactly can you calculate the space, that your encrypted column would require? There is a formula, that gives you a precise number of bytes for each encrypted cell. That formula is based on the cypher algorithm, used to encrypt the value.

1 + 32 + 16 + (FLOOR(DATALENGTH(cell_data)/16) + 1) * 16

 

Example: source column is DECIMAL(29,2) – as the precision here is 29, so the bytes required to store the value are 17.

1 + 32 + 16 + (FLOOR(17/16) + 1) * 16 = 81

 

There is also a reference table on fixed data types in our MSDN article on the subject - Always Encrypted Cryptography

Based on this formula, you can now evaluate your additional requirements and be prepared.