Publishing database changes with SSDT that include adding columns to existing table to the target database through Project

Question

Publishing database changes with SSDT that include adding columns to the existing table in the target database(which was previously created through SSDT).Instead of creating a new SSDT database. Trying to add a column through table create a script or through alter table script.i was Encountered by the same error. Even tried through Schema Compare still the table in the target database encountering with the same error. How to alter the changes to the target database objects through SSDT publishing?

RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT;

I tried by checking out the

1. Block incremental deployment if data loss might occur.

2. Don't ALTER replicated objects.

3. Do not alter Change DATA Capture Objects.

Answer 1

This is happening because you've added the column in the middle of the CREATE TABLE script. This causes a "table rebuild" to happen. I talked about this on my blog a few months ago: SSDT problems: Table Rebuilds

The section on column ordinality is the specific problem you have. Say you have a table like this:

CREATE TABLE [dbo].[Post]
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [PostType] VARCHAR(10) NOT NULL
);

And then add a column in the middle of the column list:

CREATE TABLE [dbo].[Post]
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [CommentCount] INT NULL, -- NEW COLUMN IN THE MIDDLE
    [PostType] VARCHAR(10) NOT NULL
);

This will "rebuild" the whole table in order to keep the underlying table metadata in sync between the model (your SSDT project) and the target database.

The solution? Just add the column to the end:

CREATE TABLE [dbo].[Post]
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [PostType] VARCHAR(10) NOT NULL
    [CommentCount] INT NULL -- NEW COLUMN AT THE END
);

This doesn't rebuild the table, and thus doesn't cause potential data loss.