lundi 7 septembre 2020

How do I determine whether a SQL Key column was created with compression or not?

Consider the following DDL:

ALTER TABLE dbo.MyTable
ADD CONSTRAINT [PK_MyKey] PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, DATA_COMPRESSION=PAGE)
GO

It's a compressed Primary Key. Tada!

What's bothering me is that I don't see any record of that compression in SSMS?

  • I don't find it in the Table, Column or Key Properties dialogs, from the context menus of the Object Explorer.
  • If I ask SSMS to script => CREATE the key, then it doesn't include that DATA_COMPRESSION setting. I get:
/****** Object:  Index [PK_MyKey]    Script Date: 07/09/2020 11:01:16 ******/
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_MyKey] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

So ... is it possible to tell whether or not an existing key (which I didn't create, and who's creation isn't Version Controlled :sigh:) was compressed?





Aucun commentaire:

Enregistrer un commentaire