[Bug]: SSDT Scripting differences with SQL Server Partitioned tables using Data Compression

Description

Scripting SQL Server Partitioned tables using SSMS is different from SSDT. When all the Partitions are compressed, SSMS scripts it out as WITH (DATA_COMPRESSION = PAGE), while SSDT, spells out all the Partitions.

While they may be exactly the same, there are some problems with it:

1. When we add partitions to a table, we must change all the tables involved, rather than just change the underlying Partition Function.
2. The Partitioned tables have different Partitions in different environments, which once again would force us to modify all the tables.
3. When you do the compare, depending how the script is generated the order of the partitions is different which causes a false positive. The order of the partitions appears to be random (note the example in Steps to Reproduce).

Steps to Reproduce

When scripting out Partitioned tables that use Data Compression using SSMS you get something similar to this (all partitions have DATA_COMPRESSION = page):

CREATE TABLE dbo.Invoices (
	InvoiceNumber INTEGER NOT NULL,
	InvoiceDate DATE NOT NULL,
	Col3 VARCHAR(10) NULL,
	Col4 VARCHAR(10) NOT NULL,
	CONSTRAINT PK_Invoices PRIMARY KEY (InvoiceDate, InvoiceNumber)
WITH (DATA_COMPRESSION = PAGE) ON ps_InvoiceDate(InvoiceDate)
) ON ps_InvoiceDate(InvoiceDate)

However, the same table using SSDT scripts out like this:

CREATE TABLE dbo.Invoices (
	InvoiceNumber INTEGER NOT NULL,
	InvoiceDate DATE NOT NULL,
	Col3 VARCHAR(10) NULL,
	Col4 VARCHAR(10) NOT NULL,
	CONSTRAINT PK_Invoices PRIMARY KEY (InvoiceDate, InvoiceNumber)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS (5), DATA_COMPRESSION = PAGE ON PARTITIONS (2), DATA_COMPRESSION = PAGE ON PARTITIONS (4), DATA_COMPRESSION = PAGE ON PARTITIONS (1), DATA_COMPRESSION = PAGE ON PARTITIONS (3)) ON ps_InvoiceDate(InvoiceDate)
) ON ps_InvoiceDate(InvoiceDate)

Affected Area

• Connection dialog (SQL Server | Azure browse/Fabric browse)
• Query editor
• Query results panel
• Object Explorer
• GitHub Copilot integration
• Preview/Edit data
• Table Designer
• Schema Designer
• Schema Compare
• Local SQL Server Container provisioning
• SQL database in Fabric provisioning
• DACPAC/BACPAC export/import
• SQL Database projects
• Query Plan Visualizer
• Other (please describe below)

If you selected "Other", please describe the affected area

No response

Environment Information

Extension version: 8.4.4

Visual Studio Code:
Version: 1.108.1 (system setup)
Commit: 585eba7c0c34fd6b30faac7c62a42050bfbc0086
Date: 2026-01-14T14:55:44.241Z
Electron: 39.2.7
ElectronBuildId: 12953945
Chromium: 142.0.7444.235
Node.js: 22.21.1
V8: 14.2.231.21-electron.0
OS: Windows_NT x64 10.0.17763

Installed Extensions:
ms-mssql.data-workspace-vscode: 0.6.3
ms-mssql.sql-bindings-vscode: 0.4.1
ms-mssql.mssql: 1.38.0
ms-mssql.sql-database-projects-vscode: 1.5.5

Confirmation

• I have searched existing issues and couldn't find a match
• I want to work on this issue

Start with the SQL Database projects and Schema Compare areas using the partitioned-table examples in the reproduction steps. Compare SSDT's generated definitions with the SSMS form, including partition ordering and compression representation; done means equivalent definitions no longer require table changes or produce false-positive comparisons.