Home Forums SQL Server 2008 SQL Server 2008 - General If I compress a primary key, am I compressing the whole table --> P-key with DATA_COMPRESSION RE: If I compress a primary key, am I compressing the whole table --> P-key with DATA_COMPRESSION

  • March 5, 2013 at 4:40 pm

    #1593686

    Steve Malley (3/5/2013)

    Can someone explain how data compression works at the index level? If I compress a primary key, am I compressing the whole table [since the data pages are leaf pages]?

    Depends whether the primary key is clustered or not. There's no requirement that it is.

    If you compress the clustered index, you are compressing the table, if you compress a nonclustered index, you're compressing just the index.

    Have a look for the performance benchmarks microsoft did (cat team I think). They got performance gains in all but one case, even when compressing active, in-use portions of the database. Less data to be moved around, less memory required resulted in improved performance.

    I'll see if I can find the link in the morning.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass