August 16, 2010 at 10:39 am
I did resotred a database form SQL 2005 to SQL 2008 box, now how do i use data comresion option on this existign database.
August 16, 2010 at 10:42 am
I want to use compression on the whole database, which one would be better ROW or PAGE compression?
August 16, 2010 at 11:25 am
hope this article will be useful for you
http://www.sql-server-performance.com/articles/dba/Data_Compression_in_SQL_Server_2008_p1.aspx
----------
Ashish
August 18, 2010 at 11:54 pm
Nice reference Ashish, good link for understanding
August 20, 2010 at 7:01 am
How would be the performance if i do ROW and PAGE compression both on a database including indexes, is it preferable?
Here is my understanding, though i do PAGE compression it will still do ROW compression where ever it is required that tells me that i have to go ahead with PAGE compression by default not worrying about ROW level if i have to save more space.
is there a way we can modify an existing table including index and all partitions rebuild using PAGE compression in a single sql statement ?
August 22, 2010 at 8:45 pm
PAGE compression uses algorithm for compression so there is will be minor performance impact compared to ROW where as you can get very good compression ratio.
If the table is heavily used OLTP application then you can go for ROW compression else PAGE is fine. You can write a ALTER TABLE Command to change the compression level.
I've written a sample query to fetch all the tables in the database and process row compression. Just run the query and copy the output and paste it in query window and execute it. To change the compression type change ROW to PAGE
SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id)+'].['+RTRIM(LTRIM(name)) +'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)' FROM sys.tables
Regards..Vidhya Sagar
SQL-Articles
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply