As an option you can initially create Null-able column, then update your table column with valid not null values and finally ALTER column to set NOT NULL constraint:

ALTER TABLE MY_TABLE ADD STAGE INT NULL
GO
UPDATE MY_TABLE SET <a valid not null values for your column>
GO
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL
GO

Another option is to specify correct default value for your column:

ALTER TABLE MY_TABLE ADD STAGE INT NOT NULL DEFAULT '0'

UPD: Please note that answer above contains GO which is a must when you run this code on Microsoft SQL server. If you want to perform the same operation on Oracle or MySQL you need to use semicolon ; like that:

ALTER TABLE MY_TABLE ADD STAGE INT NULL;
UPDATE MY_TABLE SET <a valid not null values for your column>;
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL;

You will either have to specify a DEFAULT, or add the column with NULLs allowed, update all the values, and then change the column to NOT NULL.

ALTER TABLE <YourTable> 
ADD <NewColumn> <NewColumnType> NOT NULL DEFAULT <DefaultValue>

I'll share how I have done this in the past. It is designed to solve the specific limitation of pre-deployment scripts that you call out in your second point:

Adding the column in a pre-deployment script will fail the publish when it automatically tries to create the same column, a second time (even if the pre-deployment script is written to be idempotent)

Why pre-deployment scripts don't work for this

When you deploy an SSDT project, the way it stitches thing together is like this (a bit simplified, but in general):

1. Do the "schema compare" between the source (dacpac file) and the target (database)
2. Generate a deployment script based on the results of that compare
3. Process any pre-deployment scripts in the dacpac (doing token replacement, etc.) and insert the contents into the beginning of the deployment script
4. Do the same for post-deployment scripts, appending to the end of the deployment script

When a new column exists in the dacpac and not in the target database, step #2 will generate code to add that column. So if the pre-deployment script adds this column, the main part of the script will fail (because it assumes the column doesn't exist, based on the results of the schema compare in step #1)

Solution: pre-SSDT script

Martin Smith mentioned this option in a comment, and it's the solution that has worked best for me so far:

We use premodel scripts in our deplyment pipeline. This is not something that is part of SSDT but a step that runs before the dacfx publish. So in this case the premodel script could add the column with the desired values and make it not null and by the time the publish happens it is already in the state expected by SSDT so it doesnt have anything to do. I'm still yet to find much use for predeploy scripts. – Martin Smith

The steps to implement this solution in general are:

1. Create a script in the SSDT project to hold your "pre-SSDT" T-SQL code
   • Depending on how your deployment process works, code in these files should probably be idempotent
2. Make sure to set this script to "Build Action=None" and "Copy to Output Directory=Copy always" - the "copy always" option is especially important, as the deployment process needs to be able to find this script in your deployment artifacts
3. In your deployment process, locate and run this script (or scripts) before the SSDT schema compare occurs
4. Once that script has been executed successfully, you can engage DacServices / DacFx / whatever to complete your deployment as usual

In the end, this allows you to add the column using whatever custom code you like, populated using complex business logic, in the pre-SSDT script.

You also add the column definition in the SSDT project (so source control still matches the real life state of the database). But when the schema compare runs, it sees no changes related to that column (because you've already deployed it).

Other uses of pre-SSDT

I often find when testing deployments that SSDT performs a "table rebuild" operation* when it's completely unnecessary. This is where a new table is a created with the updated schema, all data is copied to that table, the old table is dropped, and the new table is renamed to replace the old table.

This can lead to massive transaction log file growth and other problems if the table is large. If I notice that a schema change is causing this, I'll instead make the change myself in pre-SSDT (which is usually a simple ALTER TABLE statement) and avoid the table rebuild.

Is this a good idea?

I think so. If you read Critiquing two different approaches to delivering databases: Migrations vs state by Alex Yates, this is essentially combining the two approaches a bit. SSDT is state based, but we incorporate a migration step (before SSDT) to handle some of the more complex scenarios that SSDT just has no way of dealing with in a general way.

In doing some searching while writing this answer, this is actually a very common approach discussed in the SSDT user community once you know what to search for. I've seen it called:

• pre-compare
• pre-model
• pre-DAC
• pre-SSDT

Etc. Here's a great article that covers a lot of the points that I mentioned above:

Pre-Compare & Pre-Deployment Scripts to SSDT

And one from Red Gate (in the #4 – Changing from system type to user defined type section) that also refers to this as pre-compare:

How to Fix Ten SSDT Deployment Snags, With or Without ReadyRoll

So what's the point of pre-deployment scripts?

Martin points out that he hasn't found "much use for predeploy scripts." I tend to feel the same way. But there are scenarios where they can be useful.

One example a coworker pointed out to me was storing some data in a temp table to be used in the post deployment script (say you're moving a column from one table to another).

*The table rebuild looks like this, which is horrifying, right?

GO
PRINT N'Starting rebuilding table [dbo].[MyTable]...';


GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_MyTable] (
    [Id] BIGINT IDENTITY (1, 1) NOT NULL,
    -- etc, other columns
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[MyTable])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_MyTable] ON;
        INSERT INTO [dbo].[tmp_ms_xx_MyTable] ([Id], ...)
        SELECT   [Id],
                 -- etc, other columns
        FROM     [dbo].[MyTable]
        ORDER BY [Id] ASC;
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_MyTable] OFF;
    END

DROP TABLE [dbo].[MyTable];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_MyTable]', N'MyTable';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

You just set a default value in the new columns and that will allow you to add them.

alter table table_name
    add column_name datetime not null
       constraint DF_Default_Object_Name default (getdate())

or this one for a varchar field.

alter table table_name
    add column_name varchar(10) not null
       constraint DF_Default_Object_Name default ('A')

You can also drop the default if you do not need it after you added the column.

alter table table_name
    drop constraint DF_Default_Object_Name

You can use following syntax:

BEGIN TRANSACTION

ALTER TABLE [MySchema].[MyTable] 
        ADD [MyColumn] VARCHAR(10) NOT NULL CONSTRAINT MyTableDefault DEFAULT 'ValueForExistingData';

ALTER TABLE [MySchema].[MyTable] 
        DROP CONSTRAINT MyTableDefault;

COMMIT

On Enterprise edition SQL Server 2012+ both statements will be metadata only operations, so you will not have to update all data pages and the whole operation will take milliseconds.

You cannot add a new NOT NULL column without assigning non-NULL values to existing rows (via a default constraint). However, you can add a NULL column and ensure only NOT NULL values going forward by specifying a check constraint that prohibits NULL values along with the NOCKECK option so that existing data are not validated:

CREATE TABLE dbo.Person
(
    FirstName NVARCHAR(50) NOT NULL,
    LastName  NVARCHAR(50) NOT NULL,
    DOB       DATE NOT NULL
)
INSERT INTO dbo.Person (FirstName, LastName , DOB)
    VALUES(N'foo', N'bar', '20010101');
GO
--add new column and CHECK constraint with NOCHECK
ALTER TABLE dbo.Person WITH NOCHECK
    ADD Address nvarchar(50) NULL
    CONSTRAINT CK_Person_Address CHECK (Address IS NOT NULL);
GO
--this fails due to check constraint violation
INSERT INTO dbo.Person (FirstName, LastName , DOB)
    VALUES(N'foo2', N'bar2', '20020101');
GO
--this succeeds
INSERT INTO dbo.Person (FirstName, LastName , DOB, Address)
    VALUES(N'foo2', N'bar2', '20020101', N'non-null address');
GO

This method provides the proper NULL semantics for existing persons that have no addresses yet guarantee the desired data integrity for new persons.

specify the datatype of the column

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL;

alter table movie_archive alter column RecordID INTEGER Not null;