alter table and Encrypted Columns

Considerations for using encrypted columns.

When used to add or modify an encrypted column, alter table may take a significant amount of time if the table contains a large number of rows.
Modifying a column for encryption may cause the row size of the table to increase.
You cannot use alter table to encrypt or decrypt a column:
- If the column belongs to a clustered or placement index. To encrypt or decrypt such a column, drop the index, alter the column, and re-create the index.
- If the table has a trigger defined. Drop the trigger before you modify the column. Afterwards, re-create the trigger:
If you modify the type of an encrypted column belonging to a clustered or placement index, the index is out of order, and alter table displays an error. Drop the index before modifying the type. Afterwards, re-recreate the index.
You can encrypt these datatypes:
- int, smallint, tinyint
- unsigned int, unsigned smallint, unsigned tinyint
- bigint, unsigned bigint
- decimal and numeric
- float4 and float8
- money, smallmoney
- date, time, smalldatetime, datetime, bigdatetime
- char and varchar
- unichar, univarchar
- binary and varbinary
- bit
The underlying datatype of encrypted data on disk is varbinary. Null values are not encrypted.
Modifying the datatype of the encrypted column belonging to a clustered or placement index results in the index being out of order, and alter table displays an error. Drop the index before modifying the type, after which you re-create the index.
alter table reports an error if you:
- Change a computed column to an encrypted column, or change an encrypted column to a computed column
- Enable a column for encryption where the column is referenced in an expression used by a computed column
- Change a computed column to reference an encrypted column.
- Encrypt a column that is a member of a functional index
- Specify an encrypted column as a partition key
- Enable a column for encryption that is already used as a partition key

Note: Referential integrity between encrypted columns is supported when the columns are encrypted with the same key. For details, see Encrypting Data in the Encrypted Columns Users Guide.