Data Compression (Table Level Compression) Video Tutorials

Leave a comment (1) Go to comments

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:

  1. Row compression and
  2. 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.

The following Video examples shows how to compress data in new and existing tables and indexes.

 

The following code examples show how to compress data in new and existing tables and indexes.

USE [AdventureWorks] 

go

/*

Estimating How Much space / Storage can be saved by implementing Compression either by Row level Compression of by Page Level Compression

*/

EXEC sp_estimate_data_compression_savings 'Sales', 'SalesOrderDetail', NULL, NULL, 'ROW' ;

go

EXEC sp_estimate_data_compression_savings 'Sales', 'SalesOrderDetail', NULL, NULL, 'PAGE' ;

GO

/*

Estimating storage space savings where first parameter is the schema name, the second parameter is the object name, the third parameter is the index id, the fourth parameter is the partition id and the last parameter is the compression type.

*/

 

/*

Enabling Compression on a Table

*/

 

ALTER TABLE Sales.SalesOrderDetail

REBUILD WITH (DATA_COMPRESSION = ROW);

 

–OR

ALTER TABLE [Sales].[SalesOrderDetail] REBUILD PARTITION = ALL

WITH

(DATA_COMPRESSION = PAGE

) 

Compressing data is a CPU-intensive operation, so multiple processors can reduce the time that it takes to compress data. You can use the MAXDOP option of the
ALTER TABLE, CREATE INDEX, and ALTER INDEX statements to configure the number of processors that are used to perform the operation.

Note:

  1. Row compression enables you to store rows on a page, but it does not change the maximum size of a row in a table or an index.
  2. Data compression is only available in the Enterprise and Developer editions and higher editions of SQL Server 2008 and SQL Server 2008 .
  3. Enabling compression is performed via rebuilding an index/table – CAN be online
  4. Can NOT currently work with XML, BLOB, MAX data types

Tags : SQL Server 2008 Data Compression,Creating Compressed Tables and Indexes,Implementing Data Compression in SQL Server,Types of data compression in SQL Server,An Introduction to Data Compression in SQL Server 2008,SQL Server 2008 and Data Compression,Compression Features in SQL Server 2008,SQL Server 2008 Database Compression,Quick overview of Data Compression SQL server 2008,SQL Server Data Compression,Data Compression Commands for SQL Server 2008 and 2008 R2, how to enable data compression on sql server, enable data compression,Using Data Compression with SQL Server 2008 and 2008 R2,Using Data Compression with SQL Server,Microsoft SQL Server Data Compression,Data Compression Strategy Capacity Planning ,sql server Data Compression Strategy,Page compression in SQL Server,row compression in SQL Server,SQL Server 2008 Data Compression,Compressing SQL SERVER DAtabase,Compress SQL SERVER DAtabase,sql server Types of Data Compression,How SQL Server 2008 Data Compression works,SQL Server 2008 backup compression pros and con, SQL Server backup compression pros and con,Data Compression Using the SQL Server Vardecimal Storage Format,SQL SERVER – Data and Page Compressions,Data and Page Compressions,SQL Server White Paper Data Compression,SQL Server Compression video, how to enable compression in sql server video, video sql server compression, video row compression, video page compression, video sql server compression, video implement sql server compression

EOF - Data Compression (Table Level Compression) Video Tutorials, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Trackbacks and Pingbacks: