Two Questions in regards to UCS2 compression answered

I described UCS2 compression in the last article. It actually didn’t miss on questions on it as reaction of the blog. So let me try to address two of the questions in this blog

One of the questions was how one would be able to detect whether UCS2 compression is used? The best answer one can give sounds like: For the introduction of UCS2 compression two of the goals were to have UCS2 compression transparent and the immediate usage of it on tables which were compressed either ROW or PAGE Dictionary as soon as the database is running under SQL Server 2008 R2. This resulted in the fact that there was no sense in having an extra indicator for UCS2 compression in one of the system tables. The fact that a table is ROW or PAGE compressed already states that UCS2 compression is used when running on SQL Server 2008 R2.

The answer given above immediately triggered the second question: ‘How do I get the data which had been compressed under SQL Server 2008 UCS2 compressed’. The answer is: All data which will be modified in a compressed table under SQL Server 2008 R2 will be stored in UCS2 compressed fashion. However this doesn’t take care of the data which is read only since it might be older fiscal data or payroll data. The only way to get the contents of these tables completely into UCS2 compressed format is to rebuild the clustered index on those tables. As with deploying database compression originally under SQL Server 2008, one would need to rebuild the clustered index on SQL Server 2008 R2 to get ALL the data into UCS2 compressed form.

Cheers out of Wells, Nevada where I have an overnight stop on my drive to Phoenix, AZ to attend SAPTechED