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.
