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.