Creating Compressed Databases

Considerations that apply when creating compressed databases.

  • The compression setting for create table . . .with compression overrides the create database compression setting.
  • Temporary tables created with select into do not inherit the compression level from the database.

  • The default setting for compression in the model database is none (data compression is off for all databases based on model).

  • When you enable data compression in tempdb or other temporary databases, temporary tables created in a session or inside stored procedures do not inherit tempdb’s compression level.

  • The database to which a task is bound determines the compression level of the temporary tables it creates.