microsoft / microsoft/DacFx

Fabric Data Warehouse: tables with IDENTITY columns are rebuilt on EVERY deployment (phantom seed/increment diff) - a redeploy with zero changes fails on SET IDENTITY_INSERT

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

Description

### Follow-up to #747

#747 was closed for the original CREATE-side bug (explicit `IDENTITY (1, 1)` scripting), with
@llali inviting new issues for anything else: "closing this since the original bug was fixed.
if there are other issues, please create new one." This is that follow-up, for the
*comparison-side* defect several commenters reproduced in that thread (second deployment
failing with no table changes).

### Steps to reproduce

1. SQL project targeting **Microsoft Fabric Data Warehouse** containing:

```sql
CREATE TABLE dbo.Example (
[Id] bigint IDENTITY NOT NULL,
[Payload] varchar(100) NULL
);
```

2. `sqlpackage /Action:Publish` to a Fabric warehouse -> **succeeds** (plain CREATE, no seed/increment scripted - the #747 fix works).
3. Run the exact same publish again - same dacpac, **zero source changes** -> **fails**:

```text
Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 102, Level 15, State 12, Line 21
Incorrect syntax near 'IDENTITY_INSERT'.
Error SQL72045: Script execution error. The executed script:
CREATE TABLE [dbo].[tmp_ms_xx_Example] (
[Id] BIGINT IDENTITY NOT NULL,
[Payload] VARCHAR (100) NULL
);
IF EXISTS (SELECT TOP 1 1 FROM [dbo].[Example])
BEGIN
SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Example] ON;
INSERT INTO [dbo].[tmp_ms_xx_Example] ([Id], [Payload]) ...
```

Note the failure is at **parse time** (Msg 102, Level 15): `SET IDENTITY_INSERT` is not in
Fabric DW's T-SQL grammar, so the batch fails even when the table is **empty** - the
`IF EXISTS` row guard never gets a chance to run.

### Evidence that the diff is phantom seed/increment

`sqlpackage /Action:DeployReport` with the identical dacpac against the already-deployed target
(SqlPackage 170.4.83.3):

- Default options: the plan contains exactly one `TableRebuild` operation **per IDENTITY
table** - and no operations at all for any non-IDENTITY table in the same database.
- Adding `/p:IgnoreIdentitySeed=True /p:IgnoreIncrement=True`: the deploy report is
**completely empty** (``) - a clean no-op,
as expected for a no-change redeploy.

### Root cause

The dacpac model carries the IDENTITY property with implicit SQL Server defaults
(seed 1, increment 1). Fabric Data Warehouse cannot express seed/increment at all - per the
docs (https://learn.microsoft.com/en-us/fabric/data-warehouse/identity): "Within Fabric Data
Warehouse, you can't specify a custom starting value or increment; the system manages the
values internally" and "Defining a seed and increment isn't supported." So the model
reverse-engineered from the target can never match the source on those properties, the
comparison never converges, and DacFx plans a rebuild-and-swap it cannot execute on this
platform.

### Why the #747 roadmap answer doesn't resolve this

The closing guidance in #747 pointed to two upcoming engine features. Neither fixes this defect:

1. **`SET IDENTITY_INSERT` support** (projected April-May in that thread) had not shipped as of
2026-08-03 (the repro above is current). More importantly, when it ships it only removes the
*error*: the phantom diff remains, so every deployment would then **silently full-rebuild
every IDENTITY table** (complete table copy per deploy) instead of failing loudly.
2. **ALTER IDENTITY column support** cannot resolve a seed/increment diff on Fabric - reseeding
is unsupported on the platform, so there is no expressible ALTER for the difference DacFx
is detecting.

### Requested fix

For the Fabric Data Warehouse target platform, stop modeling/comparing IDENTITY seed and
increment (i.e., behave as `IgnoreIdentitySeed=True` + `IgnoreIncrement=True` by default),
since the platform cannot express these properties. This makes no-change redeploys a no-op and
prevents pointless full-table rebuilds, independent of the engine roadmap.

### Workaround for anyone hitting this

Add `/p:IgnoreIdentitySeed=True /p:IgnoreIncrement=True` to your publish (credit: suggested by
@akhilcs06 in the #747 thread). Verified: turns the no-change redeploy into an empty deploy
plan. Caveat: any *real* schema change to a data-bearing IDENTITY table still triggers the
rebuild and still fails on `SET IDENTITY_INSERT`.

### Environment

- SqlPackage / DacFx 170.4.83.3 (`dotnet tool`), also reproduced via Azure DevOps pipeline deploys
- SQL project SDK: Microsoft.Build.Sql 2.1.0
- Target: Warehouse in Microsoft Fabric (IDENTITY columns preview)

Contributor guide

Open the contributing guide

Research direction

Start with the DacFx comparison and deployment-planning path for Fabric Data Warehouse, then reproduce the issue with sqlpackage /Action:DeployReport using the identity-table example. Compare the default report with /p:IgnoreIdentitySeed=True /p:IgnoreIncrement=True; done means an identical redeploy produces an empty plan without a generated SET IDENTITY_INSERT rebuild script.

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
Quiet
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.