Changing Primary Key Constraint Name Causes Table Rebuild
- 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
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