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

i'm trying update database maintained , deployed using database project (.sqlproj) in visual studio 2012. easier sql server management studio, in case have deploy using dacpac.

what correct way change column not nullable, using dacpac , without risking data loss?

a nullable column added table. need publish update sets column not null , sets default. because there rows in table, update fails. there setting 'allow data loss' isn't option , update should not result in data loss. here's simple example shows 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 database , add rows, @ least 1 row should have null hellostring.

change table definition 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 published.

error:

rows detected. schema update terminating because data loss might occur.

next, tried add pre-deployment script set null 'hello':

update hello set hellostring = 'hello' hellostring null 

this publish attempt fails, same error. looking @ auto generated publish script clear why, seems incorrect behavior.

1. the not null alteration applied before default added
2. the script checks rows, doesn't matter whether there nulls or not.

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

/* column hellostring on table [dbo].[hello] must changed null not null. if table contains data, alter script may not work. avoid issue, must add values column rows or mark allowing null values, or enable generation of smart-defaults deployment option. */  if exists (select top 1 1 [dbo].[hello])     raiserror (n'rows detected. schema update terminating because data loss might occur.', 16, 127) 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' [hellostring]; 

seen in sql server 2012 (v11.0.5343), sql server data tools 11.1.31009.1

when publishing dacpac using ssms, you'll not have access full set of publish options available when publishing sqlpackage.exe or visual studio. suggest publishing either sqlpackage.exe or visual studio , enabling "generate smart defaults, applicable" option. in case of sqlpackage.exe, run 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 case of visual studio, you'd check generate smart defaults option in advanced publish options dialog.