More Detailed about Data Compression can be seen at http://sqlserver-training.com/data-compression-table-level-compression-video-tutorials Follow us at ---------------------- Website : http://sqlserver-training.com/ Twitter : http://twitter.com/dbatag/ Facebook : http://facebook.com/dbatag/ Linked In : http://in.linkedin.com/in/dbatag/ Subscribe to Emails : http://feedburner.google.com/fb/a/mailverify?uri=Sqlserver-training/ ================================ Data Compression feature of SQL Server 2008, can reduce the storage footprint of your databases. Often, data compression leads to a degradation in performance; however, SQL Server data compression simply stores data more efficiently in the rows and pages, which means that the in-memory storage when the data is in use is also reduced. In SQL Server 2008, you can compress data for the following objects: A table that is stored as a heap. A table that is stored as a clustered index. A nonclustered index. An indexed view. A partition in a partitioned table or index. SQL Server supports two types of data compression: Row compression and page compression Row Compression Row compression is a more efficient version of data in-row storage. Conceptually, it is based on the SQL Server 2005 vardecimal storage format, which eliminates the leading and trailing zeros in decimal values. Row compression in SQL Server 2008 is a whole new storage structure that reduces the size of the metadata that is required for variable-length columns. It uses no space to store zero and null values, which results in less space being required to store a row. Page Compression Page compression is a superset of (and implies) row compression. It also takes advantage of column prefix compression and page dictionary compression. In column prefix compression, if many columns start in the same way, the repeated information is replaced with a code. For example, if several product descriptions start with the word "blue...," blue could be replaced with "1." Page dictionary compression finds duplicate values on a page, stores the value in a dictionary, and replaces the duplicate values with a link to the item in the dictionary. Implementing Data Compression You can implement both types of data compression when you create a table or index, or by altering existing tables and indexes. You can do this by using Transact-SQL code or the Data Compression Wizard.

• published: 17 Aug 2010
• views: 5965