SQL Server Row Compression

Row compression saves storage space for each row by internally compressing each fixed length numeric, date/time, or character data type. For example, if DBA/Developer implemented row compression for the Invoices and ABC tables in this scenario, columns in the tables would use only the space required, rather than using the actual number of bytes for each data type. For example, if DBA/Developer’s company had only 200 sales representatives, with SalesRepIDs between 1 and 200, each row would use only one byte to store the SalesRepID instead of using the four bytes that an int data type requires. DBA/Developer can implement compression on a table when it is created or afterwards using the ALTER TABLE statement. In this scenario, DBA/Developer could implement row compression for the Invoices and ABC tables using the following statements:

ALTER TABLE Invoices
REBUILD WITH (DATA_COMPRESSION=ROW);
ALTER TABLE ABC
REBUILD WITH (DATA_COMPRESSION=ROW);

DBA/Developer can also implement compression at the page level by specifying the DATA_COMPRESSION=PAGE option. Page compression is implemented internally by SQL Server to minimize the space required to store duplicated data. SQL Server uses a column- prefix or page-dictionary compression technique to eliminate redundant data within each page. When a DBA/Developer implements page compression, SQL Server implements also implements row compression. To meet the scenario requirements, DBA/Developer could also directly modify each of the tables' columns to use the data type that would minimize storage based on the underlying data. However, this would take excessive effort, and might not be feasible if such table changes might break existing applications. Using row compression provides the benefit of not having to modify the characteristics of each individual column, while still minimizing the required storage. Compression does have some associated overhead, especially if the table is frequently accessed or modified, because the data must be compressed and uncompressed frequently. Therefore, DBA/Developer should closely evaluate not only the storage requirements but also how the table is used before implementing compression. In this scenario, the data is rarely queried and modified. Therefore, the additional overhead would likely have no adverse effects.DBA/Developer should not use a sparse column set. Sparse columns are used to optimize the storage of NULL values when a table contains mostly NULL values for a column. Column sets can be used with a table that contains sparse columns to be able to return all sparse column values as XML. In this scenario, the details about the underlying data were not provided, and no columns were mentioned as containing mostly NULL values.DBA/Developer should not implement FILESTREAM storage. FILESTREAM storage is implemented to store large binary objects, such as image or video files, as files on the file system and be able manage to them using Transact-SQL. FILESTREAM data can also be accessed using Win32 APIs. FILESTREAM storage is tightly integrated with most database functionality, including backup and recovery. When DBA/Developer take a database backup, FILESTREAM storage is also backed up unless DBA/Developer override this functionality by performing a partial backup. To create a table that can store FILESTREAM data, DBA/Developer create a table that contains a column of the varbinary(max) data type and include the FILESTREAM attribute.DBA/Developer should not compress the non clustered indexes for both tables. Although DBA/Developer can implement compression for indexes, doing so would not minimize storage for the tables as required in this scenario. DBA/Developer might choose to compress indexes to minimize storage for an index. To compress a non clustered index named IX_ProductID on the ABC table, DBA/Developer could use the following statement:

ALTER INDEX IX_ProductID ON ABC
REBUILD WITH (DATA_COMPRESSION=PAGE);