May 18, 2009 at 3:32 am
Hi Friends,
We are to compress data in Sql server 2000, 2005 and 2008.
We are using Standard edition and so we could not use the Vardecimal concept for the data compression.
Could any one suggest a common compression technique which works in all sql server 2000,2005 and 2008.
Note : we use
create table cmprs_test compress
as select * from parent_i
in oracle to accomplish data compression in oracle.
Could we have something similar in sql server.
Thanks.
May 18, 2009 at 5:11 am
With that Oracle example you are creating a new table from an existing table. The outcome may be that the new table uses less space than the existing table due to the way it can write to the blocks but it is not compression. Is that what you want to do in SQL Server or actual compression of the data?
May 18, 2009 at 6:00 am
akila.baskaran (5/18/2009)
Could any one suggest a common compression technique which works in all sql server 2000,2005 and 2008.
There isn't one.
SQL 2005 (from SP2 onwards) has vardecimal, SQL 2008 has row and page compression, but there's no compression at all in SQL 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2009 at 6:47 am
I don't know of, nor have I heard of any mechanisms for data compression prior to SQL Server 2008. You can get third party software that will compress backups for SQL Server 2000 & 2005, but not data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 18, 2009 at 8:20 am
hi,
Any way either reducing the data size or the table size should be fine.
May 18, 2009 at 8:20 am
hi,
Any way either reducing the data size or the table size should be fine.
May 18, 2009 at 9:30 am
You can reorganize the database using a maintenance plan or by shrinking the database in Enterprise Manager/Management Studio. This will do the equivalent of what you are doing in Oracle (ie: utilise more of the space in the blocks in which the data resides) but against the entire database. Actual data compression is only available in SQL 2008
May 18, 2009 at 10:13 am
DNA (5/18/2009)
You can reorganize the database using a maintenance plan or by shrinking the database in Enterprise Manager/Management Studio.
However note that shrinking will badly fragment all indexes and may well hinder performance. Also, next time there's any data added, the DB will grow again. That's an intensive operation and may slow things down.
Shrink is not recommended on a production database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply