Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SqlPackage generates CREATE INDEX script with all partitions listed if DATA_COMPRESSION specified #545

Open
IVNSTN opened this issue Dec 17, 2024 · 0 comments
Labels
bug Something isn't working

Comments

@IVNSTN
Copy link

IVNSTN commented Dec 17, 2024

  • SqlPackage or DacFx Version: 162.5.57.1
  • .NET Framework (Windows-only) or .NET Core:
  • Environment (local platform and source/target platforms):
    • win 10 x64
    • ms sqlserver 2019
    • compatibility level 150

Steps to Reproduce:

  1. Define partition schema and function with many partitions
  2. Define partitioned table and partitioned index for it, set DATA_COMPRESSION level without listing of all the partitions from partitioning schema
  3. Generate publish script
  4. See generated CREATE INDEX statement in publish script with DATA_COMPRESSION defined per partition

Index defined like this:

CREATE TABLE dbo.tbl_test (
    id          INT NOT NULL IDENTITY(1,1),
    dummy       INT NOT NULL,
    db_date     DATETIME2(7) NOT NULL DEFAULT SYSDATETIME(),
    CONSTRAINT pk_test PRIMARY KEY CLUSTERED (id ASC, db_date ASC)
        WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE) ON ps_test(db_date)
)
GO

CREATE UNIQUE NONCLUSTERED INDEX ix_test
    ON dbo.tbl_test (dummy, db_date)
    WITH (DATA_COMPRESSION = PAGE)
    ON ps_test(db_date);
GO

Publish script looks like this:

...

CREATE UNIQUE NONCLUSTERED INDEX [ix_test]
    ON [dbo].[tbl_test]([dummy] ASC, [db_date] ASC)
    WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS (1), DATA_COMPRESSION = PAGE ON PARTITIONS (2), ...)
    ON [ps_test] ([db_date]);

Additional context:

We don't maintain partition schemas via SQLPROJ sources. DBA specialists do what they need at server side to maintain data partitions. During deployment we command sqlpackage to ignore partition schemas, functions, table options, table positioning on partitions. Inside SQLPROJ we keep partition schemas and functions to store link to such objects. But they are defined minimally, which is fine for us. When sqlpackage generates index creation script with hardcoded per-partition option this is unexpected behavior.

Recently we have faced this issue on our old project where partition schema and function had 8000+ partitions defined in sqlproj. Sqlpackage generated index creation script with all the 8000+ partitions listed whilst nothing alike was defined in the project sources. WITH (DATA_COMPRESSION = PAGE) was defined and it was expected that this compression level would be applied to all partitions similarly. Deployment failed with error

Msg 7722 ... invalid partition number ... partition number can range from 1 to 2193

Publish script should contain CREATE INDEX script with compression options exactly as it was defined.

Did this occur in prior versions? If not - which version(s) did it work in?

(DacFx/SqlPackage/SSMS/Azure Data Studio)

@IVNSTN IVNSTN added the bug Something isn't working label Dec 17, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
None yet
Development

No branches or pull requests

1 participant