Add support "add column statement with 'with values' when nullable" deploy option

[wesrivery]

I'm always frustrated when add column with default values and is nullable.
I always modify a statement as below.

before

ALTER TABLE [dbo].[TableName]
    ADD [ColumnName] VARCHAR (1) DEFAULT ('0') NULL;

after

ALTER TABLE [dbo].[TableName]
    ADD [ColumnName] VARCHAR (1) NULL DEFAULT ('0') WITH VALUES;

Describe the solution you'd like
Add deploy option that insert 'with values' to "ALTER TABLE ... ADD ..." statement.

[ssreerama]

Hey wesrivery (@wesrivery) , Thank you for creating an issue and reaching us. To understand more with your request, I would like to clarify few questions regarding this issue.

1. Where do you see the 'after' script 'with values' is not supportable in DacFx? I verified with a SqlPackage.exe with both the syntax that you provided and they do create a column with default constraint.
2. DacFx supports the 'with Values' for alter table add column with default constraint, can you provide the details of what scenario you are looking for?

FYI- I added the postdeploy.sql script with the dacpac and deploy/published the dacpac using the SqlPackage.exe.

Thanks.

[wesrivery]

Sai Avishkar Sreerama (@ssreerama)
Thank you for your reply.

My scenario is here.

current table definition

CREATE TABLE [dbo].[TABLE1] (
    [COL1]  DECIMAL  DEFAULT (0) NULL
);

future table definition

CREATE TABLE [dbo].[TABLE1] (
    [COL1] DECIMAL (18) DEFAULT (0) NULL,
    [COL2] DECIMAL (18) DEFAULT (0) NULL
);

generated deploy script

ALTER TABLE [dbo].[TABLE1]
    ADD [COL2] DECIMAL (18) DEFAULT ((0)) NULL;

deploy script I want

ALTER TABLE [dbo].[TABLE1]
    ADD [COL2] DECIMAL (18) NULL DEFAULT ((0)) WITH VALUES;

After generated script applied, value of COL2 is NULL in existing records.
Is there deploy option that generating 'alter table ...' script with 'with values'?

[ssreerama]

wesrivery (@wesrivery) Sorry for the delayed response, and Thank you for clear explanation with examples, very much appreciated and I'll get back to you with my findings! Thanks.