![]() ![]() If you are interested in implementing Row level compression, click the Next button to continue with the rest of the steps within the wizard to implement data compression.The trial period of the Microsoft SQL Server 2008 R2 180-day Evaluation version had come to an end on my laptop and the SQL Server had stopped working. In the Select Compression Type wizard screen, choose Row in the Compression Row drop down list and then click on Calculate to estimate the space savings.ĥ. In the Welcome to Data Compression wizard screen, click Next to continue.Ĥ. Expand Databases | AdventureWorks2008 | Tables | right click Person.Address | select Storage and then choose Manage Compression… option from the drop down list as shown in the snippet below.ģ. Connect to SQL Server Database Engine using SQL Server Management Studio.Ģ. The below TSQL Query return the space saved when Row Level Compression is enabled for Person.Address table of the AdventureWorks2008 sample database.Įstimate the Savings in Storage Space Using Data Compression WizardĭBAs can leverage the Data Compression Wizard to estimate the storage space savings when Row or Page level compression is enabled for a particular table or index within a database.ġ. Using sp_estimate_data_compression_savings inbuilt system stored procedure.Įstimate the Savings in Storage Space Using Inbuilt System Stored ProcedureĭBAs can estimate the storage space savings using the sp_estimate_data_compression_savings inbuilt system stored procedure. There are two different ways by which database administrator can estimate the savings in storage space for tables and indexes.ġ. The below table gives you an overview of the space saving that can be achieved for different languages. SQL Server Database Engine internally stores Unicode data as 2 bytes regardless of the locale chosen by the user. Unicode provides support for data stored in English, German, Hindi, Turkish, Vietnamese and Japanese locales. If you have a table which has NCHAR(N) or NVARCHAR(N) data types then once ROW or PAGE level compression is implemented, the SQL Server Database Engine automatically enables Unicode compression. ![]() ![]() This algorithm reduces the amount of disk space used by Unicode characters. In SQL Server 2008 R2 Microsoft introduced a new algorithm known as Standard Compression Scheme for Unicode (SCSU) to compress Unicode data that is stored in Row or Page compressed objects. What is Unicode Compression in SQL Server 2008 R2? The data compression feature is currently available in Enterprise, Data Center and Developer Editions of SQL Server 2008 R2. If you are not facing a CPU crunch and you have available I/O then it is a good idea to implement data compression. DBAs need to be very careful before using Database Compression as it is a very CPU intensive activity. The main advantage of using Data Compression is that it reduces the storage cost and increases the query performance by reducing I/O. If you are new to the Data Compression Feature of SQL Server then you can refer to my earlier article titled Data Compression in SQL Server 2008. ![]() Microsoft further enhanced data compression feature in SQL Server 2008 R2 (Interim Release) to compress fixed length data types such as NCHAR(N) and NVARCHAR(N). In SQL Server 2008, Microsoft enhanced Data Compression feature to compress fixed length data types such as INTEGER, CHAR and FLOAT. Data Compression was first introduced in SQL Server 2005 with Service Pack 2 when Microsoft introduced a new storage format called VARDECIMAL to store decimal and numeric data types as variable length columns. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |