Sql-server – Spatial Index Compression

compressionspatialsql-server-2008

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

Best Answer

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.

Related Question