CODEWITHSUNDEEP
sql-server
Jonathan Allen
Jonathan Allen

Reputation: 70337

What happens when you use page compression on a primary key in SQL Server?

Given that the primary key index is how the table is physically laid out, what effect if any is there by putting a WITH DATA_COMPRESSION on it?

CREATE TABLE [Search].[Property]
    (
     [PropertyId] [BIGINT]
        NOT NULL
        CONSTRAINT PK_Property PRIMARY KEY WITH (DATA_COMPRESSION = PAGE),
     [Parcel] [GEOMETRY] NULL
                         CHECK ([Parcel] IS NULL
                                OR ([Parcel].STSrid = 3857
                                    AND [Parcel].STIsValid() = 1
                                   )),
     [StreetNumber] [VARCHAR](20) NULL,
     [StreetDir] [VARCHAR](2) NULL,
     [StreetName] [VARCHAR](50) NULL,
     [StreetType] [VARCHAR](4) NULL,
     [StreetPostDir] [VARCHAR](2) NULL
    )
    WITH (
         DATA_COMPRESSION = PAGE); 

GO

Upvotes: 0

Views: 2675

Answers (2)

Rahul
Rahul

Reputation: 77934

AFAIK, It depends actually; When you enable compression on primary key (as in your post). If a clustered index is created on PK (which is default) then it will effect as table level compression (i.e, compression on the clustered index = compressing the table); whereas if it's nonclustered index then only a index compression will take place.

 [PropertyId] [BIGINT]
    NOT NULL
    CONSTRAINT PK_Property PRIMARY KEY WITH (DATA_COMPRESSION = PAGE)

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6781

This has the same effect as compressing the table, such as :

ALTER TABLE [Search].[Property]
REBUILD WITH (DATA_COMPRESSION = PAGE);

See MSDN for details on compression of indexes and tables, or MSDN for details on how page compression is implemented within SQL Server.

Upvotes: 2

Related Questions