I have a SSDT SQL project that contains a definition for a table like:

CREATE TABLE [dbo].[MyTable] (
  [Name] VARCHAR(50) NOT NULL,
  ...,
  CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([Name] ASC)
)

I need to extend this column to 100 characters.

I can do this with a manual ALTER TABLE statement easily enough:

ALTER TABLE [MyTABLE]
ALTER COLUMN [Name] VARCHAR(100) NOT NULL

However, I'd prefer to get my deployment pipeline to do this for me, but it insists that it needs to drop and recreate the table to make this change.

Is there any way to get SqlPackage/DacFX to understand that it's possible to use a simple ALTER TABLE to make this change instead of recreating the table?

I have a SSDT SQL project that contains a definition for a table like:

CREATE TABLE [dbo].[MyTable] (
  [Name] VARCHAR(50) NOT NULL,
  ...,
  CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([Name] ASC)
)

I need to extend this column to 100 characters.

I can do this with a manual ALTER TABLE statement easily enough:

ALTER TABLE [MyTABLE]
ALTER COLUMN [Name] VARCHAR(100) NOT NULL

However, I'd prefer to get my deployment pipeline to do this for me, but it insists that it needs to drop and recreate the table to make this change.

Is there any way to get SqlPackage/DacFX to understand that it's possible to use a simple ALTER TABLE to make this change instead of recreating the table?

• sql-server
• sql-server-data-tools

• 1 I would suggest a further redesign, a Name is a terrible choice for a primary key. You would be far better off using a surrogate key and then enforcing uniqueness on the Name column. Names can, and do, change. The fact that you need to increase the column's width also helps denote that things have changed. A 100 byte PK is a very poor idea. – Thom A Commented Feb 5 at 9:24
• 1 "I can do this with a manual ALTER TABLE statement easily enough:" Assuming you have data and a foreign key reference, this is going to fail.db<>fiddle – Thom A Commented Feb 5 at 9:27
• What insists to drop and recreate the table? What code or arguments did you use? You'd only get this if you tried to generate or publish a change script. Yes, the SSDT schema compare generates conservative and somewhat outdated code, at least compared to Redgate's tools. That's why the changes should never be applied to production without inspection. – Panagiotis Kanavos Commented Feb 5 at 9:36
• 2 Your Name is part of the PK, so you won't be able to change length without dropping the PK which might lead to other issues – siggemannen Commented Feb 5 at 9:44
• Surprising not true if it's not used as a foreign key reference, @siggemannen . db<>fiddle Why I denoted in that my prior comment. – Thom A Commented Feb 5 at 9:53

1 Answer 1

The wisest possible choice would be to either allow the recreation of the table, or improving your table format. Since Names change, any non-enforced reference to these Names are possible sources of inconsistency and any enforced reference to these names are possible sources of crashes.

So your best bet would be to create a numeric Id field, make that the primary key, work your way through the deployment process once and perhaps make the Name unique which will likely be alterable without great difficulties in subsequent deployments.

But, if you insist you want Name to be a primary key, the real danger is not that this is automated, but that some other alter changing a primary key like this will unknowingly automated. So, since this is an anti-pattern I would strongly advise you not to make this automatic, or, if you want to make it automatic, then have a script file with such verified and intentional anti-patterns and execute them separately.

But, if you insist on Name being a primary key and wanting to have it automatic,then look at this page: https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/tools/sqlpackage/sqlpackage-script.md

and you can play around with the properties of

• DoNotDropObjectType=(STRING)
• DoNotDropObjectTypes=(STRING)
• DropIndexesNotInSource=(BOOLEAN 'True')
• IgnoreIndexOptions=(BOOLEAN)
• IgnoreTableOptions=(BOOLEAN)
• ScriptNewConstraintValidation=(BOOLEAN 'True')