The Data-tier Application (DAC) framework incorrectly handles in-line DEFAULT contraints created by a Data-tier application deployment. This bug affects SQL Server Management Studio 2008 R2 SP1 (SSMS), SQL PowerShell scripts, and other framework implementations such as Visual Studio 2010 SP1.
Deploying a DACPAC and immediately upgrading the data-tier application using the same DACPAC file should result in no changes being detected by the DAC framework. However, the DAC framework will report in-line constraints as having been changed externally since the DACPAC deployment. The DAC framework with create an upgrade plan that DROPs and reCREATEs all in-line DEFAULT constraints.
This is a side-effect of redeploying the same DACPAC file due to Bug 737150 (deploy the DACPAC, then upgrade from the same DACPAC) to execute the DACPAC post-deployment step. However, this bug somewhat degrades the business value of detecting external changes to a SQL Data-tier application, as it the change detection will always have false positives for any database that uses column DEFAULTs.
There are no known workarounds for this bug.
As reference, SQL Server T-SQL supports three methods for the definition of DEFAULT constraints.
1) The inline method defines the DEFAULTS for a table within the CREATE TABLE DDL statement. The inline method does not support explicit names for DEFAULT constraints, which differs from other constraints such as primary and foreign keys. Inline DEFAULTS are partially supported by the DAC framework. For example:
CREATE TABLE [MyTable]
( [Id] INT NOT NULL IDENTITY(1,1),
[Name] NVARCHAR(50) NOT NULL,
[CreatedOn] DATETIME NOT NULL DEFAULT GETDATE(),
CONSTRAINT [PK_MyTable] PRIMARY KEY ([Id])
Because the inline default constraint is not explicity named, SQL Server assigns a system-generated name, such as [DF__MyTable__539830a48fa0342543b6].
2) SQL Server supports the explicit naming of a default constraint via the ALTER TABLE DDL. However, ALTER TABLE DDL is not supported by the DAC framework.
ALTER TABLE [MyTable] ADD CONSTRAINT [DF_customname] DEFAULT GETDATE() FOR [CreatedOn]
3) SQL Server supports creation of non-ANSI DEFAULT objects that can be bound to one or more column objects. However, CREATE DEFAULT is not supported by the DAC framework.
CREATE DEFAULT [DF_getdate] AS
EXEC sp_bindefault [DF_getdate], '[MyTable].[CreatedOn]'
While the DAC framework does support deployment of ANSI-style COLUMN default values, the DAC framework appears to stumble on the system generated names, such as [DF__MyTable__539830a48fa0342543b6]. The DAC framework appears to consistently apply a system generated name. Perhaps the logic for determining the generated name should be applied to a DEFAULT constraint before comparing a DACPAC against a database?
As reference, the following components were used to reproduce the problem on a single machine:
Windows 7 SP1 32 bit - v6.1.7601
Microsoft .NET Framework - v4.0.30319.261
SQL Server 2008 R2 Management Studio - v10.50.2500.0
Microsoft SQL Server 2008 R2 (SP1) Express - 10.50.2500.0 (Intel X86)
Microsoft.SqlServer.Management.SqlParser.dll - 10.50.2500.0
Microsoft.SqlServer.Management.Dac.dll - 10.50.2500.0
Microsoft.SqlServer.Management.DacEnum.dll - 10.50.2500.0