Ignore Data Compression Bug

jmbelina

The ignore data compression option does not filter out spatial indexes with data compression.

DanC

Hi @jmbelina

Do you have an example case of this, with some code for me to create the necessary objects to reproduce?

jmbelina

Yes, try the following scripts, you'll see three difference, then check the ignore data compression setting and you'll see only the difference for the spatial index.


-- database 1 - table without data compression 

create table [dbo].[featpoint]

(

    [featkey] [int] null

    , [featpointgeo] [sys].[geometry] not null

    , [featpointid] [int] not null identity(1, 1)

    , constraint [PK_featpoint]

          primary key clustered ( [featpointid] )

);

go

create nonclustered index [NC_featpoint_featkey]

    on [dbo].[featpoint] ( [featkey] )

    ;

go

create spatial index [SI_featpoint_featpointgeo]

    on [dbo].[featpoint] ( [featpointgeo] )

    using geometry_grid

    with

        (

            bounding_box = ( -180, -90, 180, 90), cells_per_object = 16

        );

go

-- database 2 - table with data compression
create table [dbo].[featpoint]

(

    [featkey] [int] null

    , [featpointgeo] [sys].[geometry] not null

    , [featpointid] [int] not null identity(1, 1)

    , constraint [PK_featpoint]

          primary key clustered ( [featpointid] )

          with ( data_compression = page )

);

go

create nonclustered index [NC_featpoint_featkey]

    on [dbo].[featpoint] ( [featkey] )

    with ( data_compression = page );

go

create spatial index [SI_featpoint_featpointgeo]

    on [dbo].[featpoint] ( [featpointgeo] )

    using geometry_grid

    with

        (

            bounding_box = ( -180, -90, 180, 90), cells_per_object = 16

        , DATA_COMPRESSION = PAGE

        );

go

(Update: I tried using code blocks to separate the two table definitions, but it didn't show up well so I removed the code formatting.)

DanC

Hi @jmbelina

Thank you for the quick reply with an example, and don't worry about the formatting!

I'm going to pass this along to the developers and get their thoughts on the issue, and I'll provide you with an update once I have one. 

DanC

Hi @jmbelina

Whilst the developers recognize this as a bug, due to their current workload and priorities they're unfortunately unable to work on it.