We can be Rebuilding Index with Compression even if it was originally not created with compressions. Let us learn how we can do it today. I had recently discussed with my client at Comprehensive Database Performance Health Check.
We had an interesting situation at my client’s place. Where we had run few tests and found that we can use compressed indexes for better performance. The compressed index has the advantage of better storage and lesser read of pages while retrieving data from it.
Now if you have an index that was originally created without compression, you can still enable the compression when you rebuild the index. Here is the script for it.
ALTER TABLE Production.TransactionHistory REBUILD WITH (DATA_COMPRESSION = ROW);
Now the script above is for the row compression, however, you can easily compress the page as well by running the following script.
ALTER TABLE Production.TransactionHistory REBUILD WITH (DATA_COMPRESSION = PAGE);
Now the question is which is better Page Compression or Row Compression? Well, there is not a simple answer to this question. As I mentioned earlier, you will have to test this entire scenario for your own environment. It is quite possible that you will find that compression itself is not recommended in your scenario at all. If you do not have time and energy for testing, I will say, just leave the default settings as it is.
Here are my few recent videos and I would like to know what is your feedback about them. Do not forget to subscribe SQL in Sixty Seconds series.
- MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
- Tuning Query Cost 100% – SQL in Sixty Seconds #181
- Queries Using Specific Index – SQL in Sixty Seconds #180
- Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
- One Scan for 3 Count Sum – SQL in Sixty Seconds #178
- SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
- COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176
Reference: Pinal Dave (http://blog.SQLAuthority.com)
