I'm trying to update a database that is maintained using a database project (.sqlproj) in Visual Studio 2012. A nullable column was added to a table. Now we want 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. 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
)

-- 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 the be incorrect behavior. The script checks for ANY rows, it doesn't matter whether there are nulls or not, and the advice in the comment (To avoid this issue, you must add values to this column for all rows) doesn't seem to work either:

/*

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 11.0.5343, Sql Server Data Tools 11.1.31009.1

• Edited by mattb77 9 hours 2 minutes ago Added sql version numbers

Hi Mattb77,

From SQL Server Management Studio aspect, you can run the below statements to implement the schema modification.

update dbo.hello set hellostring='hello' where hellostring is null
alter table dbo.hello alter column hellostring nvarchar(100) not null
alter table dbo.hello add constraint df_hellostring_hello default('hello') for hellostring

Have no much idea on developing a database project in Visual Studio(VS), regarding how to avoid such a error in VS, I got a link from google.

For questions related to database project in VS, the forum below is a much more appropriate place to ask for help.
Visual Studio database development tools

If you have any question, feel free to let me know.