SQL Server 2016 – Data Compression Not Showing When Scripting Index in SSMS

compressionindexsql serversql-server-2016ssms

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

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';

I get the correct information.

I am using this SSMS :

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

Best Answer

Make sure your scripting options are set correctly for Data Compression

Related Solutions

SQL Server Hierarchical Order – Parent-Child Tree Hierarchical ORDER BY

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];

Sql-server – Clustered index compression vs table compression – are they the same thing

If I set compression (either page or row) on the clustered index of a table, is that the same as setting compression on the table?

Yes.

From MSDN:

Use the REBUILD WITH syntax to rebuild an entire table including all the partitions in a partitioned table. If the table has a clustered index, the REBUILD option rebuilds the clustered index.

SQL Server provides options for doing both, which suggests that they are different

The reason why both syntaxes exist is because a table doesn't necessarily have a clustered index. In other words, ALTER INDEX ALL ON ... REBUILD does not affect a heap (though it will touch all nonclustereds), so the other route serves that purpose.

Also, while the documentation doesn't explicitly say so, the ALTER TABLE ... REBUILD syntax does not enable/disable compression of all nonclustered indexes on the table. It only affects the heap or the clustered index.

Finally, if you're testing this through SSMS, be aware that you may get errors when disabling compression -- script things out so you can see what's really going on.

Best Answer

Related Solutions

SQL Server Hierarchical Order – Parent-Child Tree Hierarchical ORDER BY

Sql-server – Clustered index compression vs table compression – are they the same thing

Related Question