Spatial Index Compression

Question

I am looking to turn page compression on on a spatial index. The instance is 2008 Enterprise. The index is pre-existing on a vendor supplied app. In searching through BOL, it states that compression and fillfactor are both allowed.

ALTER INDEX [ix_city_citypoint] on city 
REBUILD WITH (FILLFACTOR = 90, DATA_COMPRESSION = PAGE) 

Msg 153, Level 15, State 4, Line 12
Invalid usage of the option data_compression in the ALTER INDEX REBUILD statement.

original create:

CREATE SPATIAL INDEX [ix_city_citypoint] ON [dbo].[city]
(
    [citypoint]
)USING  GEOGRAPHY_GRID 
WITH (GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), 
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [primary]
GO

I can always ignore the index as it's small but now it's morphed into one of those "teachable" moments. Thank you for any insight

Answer 1

Your syntax for ALTER INDEX...REBUILD WITH (DATA_COMPRESSION...) is correct. The problem is actually revealed in the error message:

Invalid usage of the option data_compression in the ALTER INDEX REBUILD statement.

For a spatial index in SQL Server 2008, using the data_compression option is not supported. According to Microsoft Documentation, support for data compression was added for spatial indexes in SQL Server 2012:

DATA_COMPRESSION = {NONE | ROW | PAGE}

Applies to: SQL Server 2012 through SQL Server 2017, SQL Database.

If/when you upgrade to a newer version of SQL Server, your original ALTER INDEX statement would work to rebuild the index using PAGE compression. Alternatively, as @AaronBertrand points out in a comment, you can also use CREATE...WITH (DROP_EXISTING=ON, DATA_COMPRESSION=PAGE... to change the compression settings, however this requires re-supplying the entire index definition, where the ALTER requires you only specify the options you are altering.