Script to Compress Tables and Indexes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jeremy Egbert » Script to Compress Tables and Indexes

Script to Compress Tables and Indexes

Rate Topic

Display Mode

Topic Options

Author

Message

JeremyE

Posted Tuesday, December 15, 2009 9:26 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:14 PM
Points: 2,414, Visits: 2,680

Comments posted to this topic are about the item Script to Compress Tables and Indexes

Post #834590

Dennis Fauerbach

Posted Wednesday, November 23, 2011 7:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 10, 2012 7:35 AM
Points: 1, Visits: 49

Just looking and running this script you need to add a DISTINCT to the select it returns multiple duplicate statements for both ALTER TABLE and ALTER INDEX statements

Post #1211019

GregoryF

Posted Monday, December 12, 2011 4:59 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, June 10, 2013 8:14 AM
Points: 961, Visits: 797

You could improve upon this script by using sys.partitions to only include rebuild statements for tables that are already uncompressed. This script will rebuild all tables, regardless of whether or not they are already compressed.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Post #1220118

Soorya

Posted Sunday, January 15, 2012 11:40 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, February 02, 2012 10:24 PM
Points: 56, Visits: 195

After using row/page/index compression in SQL 2008 DB, I noticed that DB size is increased. Before compression the DB size was 69 GB. After row compression on all tables, size increased to 73GB. Please explain.

Post #1236394

GregoryF

Posted Monday, January 16, 2012 5:01 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, June 10, 2013 8:14 AM
Points: 961, Visits: 797

Soorya (1/15/2012)

Check in the GUI to make sure that they are compressed. Then check that compression will compress. Compression does not always lead to data savings, especially row compression. It's very dependent on the data that is being compressed.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Post #1236519

« Prev Topic | Next Topic »

Permissions