Whenever data is inserted into a table or an index is rebuilt, the database engine requires more space in the database, which is usually the underlying storage where the data file is located. The database engine needs the process to determine where space is available within the pages to be allocated. Therefore, the insert process must find a mixed extent with one or more pages available that can be allocated, if the index or data is more than eight pages or larger than what a free uniform extent needs to be allocated.

SQL Server 2008 has introduced the data compression feature as a part of the programmability enhancements. The main benefit of compression is that it can be enabled or disabled at object level, which means at database-wide, page-level, or row-level. In this recipe, we will go through the important steps in implementing compressed storage features for tables and indexes. When the compression option is configured...