6

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

ssms

options

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.

2
  • Was this ever answered? As stated, the Connect item didn't help much, and now it's not there anymore, it is even less helpful.
    – David
    Jul 26, 2018 at 9:32
  • Sorry, @David, I didn't figure it out. Jul 26, 2018 at 12:47

1 Answer 1

3

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.

2
  • 2
    Yeah, I've seen this Connect item, but it doesn't help much unfortunately. I'm trying to figure out what I did before this sudden change in SSMS scripting. In my case SSMS was generating DEFAULT constraints using ALTER TABLE for all tables regardless of how many rows they have. But now it started to behave as described in this Connect item - it generates the script differently for empty and non-empty tables. Aug 9, 2016 at 13:38
  • 1
    having a single row instead of an empty table solved it for me. i was trying to do the opposite and get everything into the create table statement. thanks
    – venomzx
    Jun 24, 2019 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.