SSMS suddenly starts generating DEFAULT constraints inlined into the CREATE TABLE statements instead of ALTER TABLE

Question

I've been using SSMS to generate scripts of the whole database and storing them in the source control.

It's been working fine for many years, but few days ago I noticed that SSMS started generating DEFAULT constraints as a clause inlined into the CREATE TABLE statement instead of adding a separate ALTER TABLE statement.

Here is an example of one table. This is how the script looked like before:

USE [my db name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ConsignmentGroups](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ConsignmentID] [int] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Notes] [nvarchar](255) NOT NULL,
    CONSTRAINT [PK_ConsignmentGroups] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ConsignmentGroups] ADD  CONSTRAINT [DF_ConsignmentGroups_Notes]  DEFAULT ('') FOR [Notes]
GO

Here DEFAULT constraint is scripted as a separate ALTER TABLE statement.

This is how it looks like now:

USE [my db name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ConsignmentGroups](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ConsignmentID] [int] NOT NULL,
    [Name] [nvarchar](255) NOT NULL,
    [Notes] [nvarchar](255) NOT NULL CONSTRAINT [DF_ConsignmentGroups_Notes]  DEFAULT (''),
    CONSTRAINT [PK_ConsignmentGroups] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Here DEFAULT constraint is inlined into the CREATE TABLE statement.

It didn't happen to all tables with DEFAULT constraints. There are about 60 tables with DEFAULT constraints and at least one of them is still scripted as ALTER TABLE statement. I don't think anyone touched the table in the example above (ConsignmentGroups), there were no changes to the table structure. The only change is the style of DEFAULT constraint.

The problem is that this kind of change in the T-SQL script is picked up by the source control as a change. I'd like to know how to control it to avoid it in the future.

I assume that I accidentally changed some setting in SSMS without realising what it does. Does anyone know where this setting could be?

I use SQL Server 2008 Standard

Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64) 
    Sep  3 2014 04:11:34 
    Copyright (c) 1988-2008 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

SSMS 2012

Microsoft SQL Server Management Studio     11.0.6020.0
Microsoft Data Access Components (MDAC)    6.1.7601.17514
Microsoft MSXML                            3.0 4.0 5.0 6.0 
Microsoft Internet Explorer                9.11.9600.18376
Microsoft .NET Framework                   4.0.30319.42000
Operating System                           6.1.7601

and SSMS Boost build 2.19.5952.29737

I don't remember updating any of these components or changing any settings for a while, but apparently something has changed.

---

I tried to generate these scripts using SSMS 2014 (12.0.4459.0) without SSMSBoost installed on a virtual machine that I don't usually use.

To my surprise the result was the same. The scripts generated by different versions of SSMS running from different computers are identical. This means that it is not a setting in SSMS, there must be something in the database itself.

Also, the fact that one table out of ~60 is scripted differently suggests that this setting is not database-wide, but works on a table level.

Answer 1

This 'might' be the problem, although, there does not appear to be a 'solution'. I was able to reproduce your issue. See this connect item regarding this problem:

https://connect.microsoft.com/SQLServer/Feedback/Details/895113

The SQL SMO generates for a table with defaults depends on the rowcount of the table. This makes it impossible to programmatically compare the schema.