microsoft / microsoft/DacFx

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

Open
#545 5 comments 12 reactions 0 assignees View on GitHub
bug
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

- 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:

```sql
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)

Contributor guide

Open the contributing guide

Research direction

Reproduce the issue with the partition schema, partitioned table and index, DATA_COMPRESSION option, and publish-script steps described in the issue using SqlPackage/DacFx. Inspect the publish-script generation path for CREATE INDEX options and compare its output with the source definition; done means compression remains unexpanded without enumerating every partition or producing invalid partition numbers.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.