microsoft / microsoft/DacFx

Changing Primary Key Constraint Name Causes Table Rebuild

Open
#295 7 comments 1 reaction 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: `sqlpackage 162.0.52.1`
- .NET Framework (Windows-only) or .NET Core: `.Net Core`
- Environment (local platform and source/target platforms): `Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64) Jul 19 2021 15:37:34 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Home 10.0 (Build 19045: ) `

**Steps to Reproduce:**

1. Create a new SSDT (sqlproj) project in visual studio.
2. Add a single table sql file like so:
```sql
CREATE TABLE [dbo].MyTable(
Id [BIGINT] IDENTITY(1,1) NOT NULL
)
GO
ALTER TABLE dbo.MyTable ADD CONSTRAINT PK1 PRIMARY KEY CLUSTERED (Id)
--ALTER TABLE dbo.MyTable ADD CONSTRAINT PK2 PRIMARY KEY CLUSTERED (Id)
```
3. Build and deploy the dacpac to a server using sqlpackage or vs.
4. Uncomment the `PK2` constraint and comment out the `PK1` constraint. You effectively changed the name of the primary key constraint.
5. Build and deploy the dacpac to a server using sqlpackage or vs.
6. Expected: the constraint `PK1` is dropped and the constraint `PK2` is created.
Actual: The table is rebuilt with the message `Starting rebuilding table [dbo].[MyTable]...`

Table rebuilds are expensive and unwanted, especially on large tables with millions of rows. For a small change such as changing table constraints without changing any column info a table rebuild is unnecessary. It took me a while on a large table to figure out what was causing the table to be rebuilt when this was the culprit.

Attached is the project file to help reproduce.
[Demo.zip](https://github.com/microsoft/DacFx/files/11931273/Demo.zip)

(DacFx/SqlPackage/SSMS/Azure Data Studio)

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the issue with the attached Demo.zip and its SSDT sqlproj, using the PK1-to-PK2 change described in the steps. Compare the deployment output and schema-diff behavior; done means renaming only the primary-key constraint drops PK1 and creates PK2 without rebuilding the table.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
build-system, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.