SQL72015 while adding unique id column in existing table having data

Question

ALTER TABLE [Student]
    ADD [DeptId] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() with values

While I run this script through sql server management studio it runs fine. But when I publish through visual studio sql server project it gives following error.

Warning SQL72015: The column [Student].[DeptId] on table [Student] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue you must either: add a default value to the column, mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.

I tried unchecking Block incremental deployment if data loss might occur in project > properties > debug

What's in the script if you choose to generate a script instead of publish directly? (or if you analyze the generated script) — Peter Schott

Ravindra Sinare Ravindra Sinare · Accepted Answer · 2016-01-19T09:09:20

You can find solution in the Error/Warning itself.

Possible ways to avoid it:

1) Add a default value to the column OR 2) Mark it as allow NULL values OR 3) Delete all the data/rows from table if data is not important.

SQL72015 while adding unique id column in existing table having data

1 Answers