How can I update a nullable column to be not nullable, in SQL Server using DACPAC

Question

I'm trying to update a database that is maintained and deployed using a database project (.sqlproj) in Visual Studio 2012. This is easier with SQL Server Management Studio, but in this case I have to deploy using a DACPAC.

What is the correct way to change a column to not be nullable, using DACPAC and without risking data loss?

A nullable column was added to a table. Now I need to publish an update that sets the column to not null and sets a default. Because there are rows in the table, the update fails. There is a setting to 'allow data loss' but that isn't an option for us and this update should not result in data loss. Here's a simple example that shows the problem:

CREATE TABLE [dbo].[Hello]
(
    [Id] INT IDENTITY(100,1) NOT NULL PRIMARY KEY, 
    [HelloString] NVARCHAR(50) NULL , 
    [Language] NCHAR(2) NOT NULL
)

Now publish that database and add rows, at least one row should have a null for HelloString.

Change the table definition to be:

CREATE TABLE [dbo].[Hello]
(
    [Id] INT IDENTITY(100,1) NOT NULL PRIMARY KEY, 
    [HelloString] NVARCHAR(50) NOT NULL DEFAULT 'Hello' , 
    [Language] NCHAR(2) NOT NULL
)

This cannot be published.

Error:

Rows were detected. The schema update is terminating because data loss might occur.

Next, I tried to add a pre-deployment script to set all NULL to be 'Hello':

UPDATE Hello SET HelloString = 'Hello' WHERE HelloString IS NULL

This publish attempt also fails, with the same error. Looking at the auto generated publish script it is clear why, but this seems to be incorrect behavior.

1. The NOT NULL alteration is applied BEFORE the default is added
2. The script checks for ANY rows, it doesn't matter whether there are nulls or not.

The advice in the comment (To avoid this issue, you must add values to this column for all rows) doesn't solve this.

/*
The column HelloString on table [dbo].[Hello] must be changed from NULL to NOT NULL. If the table contains data, the ALTER script may not work. To avoid this issue, you must add values to this column for all rows or mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.
*/

IF EXISTS (select top 1 1 from [dbo].[Hello])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO

PRINT N'Altering [dbo].[Hello]...';
GO

ALTER TABLE [dbo].[Hello] ALTER COLUMN [HelloString] NVARCHAR (50) NOT NULL;
GO

PRINT N'Creating Default Constraint on [dbo].[Hello]....';
GO

ALTER TABLE [dbo].[Hello] 
ADD DEFAULT 'hello' FOR [HelloString];

Seen in SQL Server 2012 (v11.0.5343), SQL Server Data Tools 11.1.31009.1

Answer 1

When publishing a dacpac using SSMS, you'll not have access to the full set of publish options that are available when publishing from SqlPackage.exe or Visual Studio. I would suggest publishing with either SqlPackage.exe or with Visual Studio and enabling the "Generate smart defaults, where applicable" option. In the case of SqlPackage.exe, you would run a command like:

"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /a:publish /sf:"C:\MyDacpac.dacpac" /tcs:"Data Source=MYSERVER;Initial Catalog=MYDATABASE;Integrated Security=true" /p:GenerateSmartDefaults=true

In the case of Visual Studio, you'd check the Generate smart defaults option in the Advanced publish options dialog.