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