2

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

6

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.

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.