-1

when I right click on SSMS and choose to script the create index:

enter image description here

I get this script, without the data_compression:

USE [APCore]
GO

/****** Object:  Index [i_agencyId]    Script Date: 07/11/2019 12:18:13 ******/
CREATE UNIQUE NONCLUSTERED INDEX [i_agencyId] ON [agy].[agency]
(
    [agencyId] ASC
)
INCLUDE (   [countryCode]) WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [NONCLUSTERED_INDEXES]
GO

But when specifically checking for the data compression using teh query below

use APCore
go

SELECT i.name, p.data_compression_desc 
 FROM sys.partitions P
 INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id
 WHERE P.data_compression > 0 and I.name = 'i_agencyId';

enter image description here

I get the correct information.

I am using this SSMS :

enter image description here

Is there a way to make ssms include data compression on the index definition when scripting?

4

Make sure your scripting options are set correctly for Data Compression

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.