SSDT Drop and Recreate Tables when nothing has changed

After a fair amount of frustration, finally found the cause, and posting as an answer as it may help others....

Finding the reason for why SSDT thought the scheme was different I think is the first point of call. SQL Scheme compare is your friend here. (In VS under Tools => SQL => New Scheme Comparison...)

Firstly thanks to @jadarnel27, was very helpful so upvote dude cheers, but wasn't the answer I'm afraid. Computed column definitions and constraint definitions were definitely candidates, as was potentially column ordering. Ironically I did have one issue with a computed column but it was not the definition, it was the fact that I didn't add NOT NULL to the end of the column definition (which is the default of course) but SSDT saw that as different everytime.

So I had to change a column from

[ColumnName] AS (CONCAT(Col1,' ',Col2)) PERSISTED

to

[ColumnName] AS (CONCAT(Col1,' ',Col2)) PERSISTED NOT NULL

then it stopped constantly dropping and re-creating the column. This showed up in Scheme Compare, not in the definition of the computed column.

Secondly, because we were using replication, SQL was adding NOT FOR REPLICATION to most of the tables that were being used in replication (reasons for is to do with the works of replication) but in essence was changing a tables DDL from

CREATE TABLE [dbo].[Whatever]
(
    [WhateverId]    INT IDENTITY(1,1) NOT NULL
    ...etc more columns
)

to

CREATE TABLE [dbo].[Whatever]
(
    [WhateverId]    INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
    ...etc more columns
)

again this showed up on Scheme compare......

and to fix, either add NOT FOR REPLICATION into the DDL source code in VS for all tables required or under the publish settings -> Advance -> Ignore tab and scroll down a bit, tick the box as below:

and then everything was disco

There are several things that can cause this kind of "table movement" or "table rebuild" action to be generated by the SSDT publish process.

Expression Standardization

The most common reason I've come across is that you have computed (or persisted computed) columns in those tables, and you're using a built-in function or expression that SQL Server wants to change to a more standard form.

I've written about this a bit on my blog: SSDT problems: deploying the same change over and over

If you do have computed columns, take a look at your source code, and then compare it to the actual stored value in your production database by running this query:

select [definition] 
from sys.computed_columns 
where [name] = 'YourColumnName';

If it's different, update your source code to match what's stored in sys.computed_columns, and you should be good to go.

As I mentioned in the blog, another cause of the rebuild could be a similar situation with a CHECK constraint on one of that table's columns.

A non-exhaustive list of things to look for in constraint or computed column definitions:

• CAST (gets changed to CONVERT)
• IN (gets changed to a list of OR statements)
• BETWEEN (gets converted to two inequality statements)

Column Ordinality

By default, if the columns as written in your source code are out of order from what they are in the destination table, SSDT will rebuild the table to get them back in the same order.

You can disable this behavior by setting the "ignore column order" property of the advanced publish options.