Unnamed default constraints become named constraints

chrisdar

I am evaluating the SQL Comparison SDK as a tool to automate in-place upgrade of databases. I have hit a snag that relates to default constraints.

I have a v. N-1 table with a date column. Using SMO to generate a create script yields:
CREATE TABLE [dbo].[PipelineLog](
[logOrder] [int] IDENTITY(1,1) NOT NULL,
[component] [nvarchar](255) NULL,
[date] [datetime] NULL,
[text] [nvarchar](max) NULL,
CONSTRAINT [PK_PipelineLog] PRIMARY KEY CLUSTERED
(
[logOrder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


In v. N the column has a default, unnamed constraint applied, which I want applied during the upgrade. In the model database, the create script for the table is:
CREATE TABLE [dbo].[PipelineLog](
[logOrder] [int] IDENTITY(1,1) NOT NULL,
[component] [nvarchar](255) NULL,
[date] [datetime] NULL,
[text] [nvarchar](max) NULL,
CONSTRAINT [PK_PipelineLog] PRIMARY KEY CLUSTERED
(
[logOrder] 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].[PipelineLog] ADD DEFAULT (getutcdate()) FOR [date]
GO

After upgrade, the default constraint is correctly applied, but it is named:
CREATE TABLE [dbo].[PipelineLog](
[logOrder] [int] IDENTITY(1,1) NOT NULL,
[component] [nvarchar](255) NULL,
[date] [datetime] NULL,
[text] [nvarchar](max) NULL,
CONSTRAINT [PK_PipelineLog] PRIMARY KEY CLUSTERED
(
[logOrder] 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].[PipelineLog] ADD CONSTRAINT [DF__PipelineLo__date__0519C6AF] DEFAULT (getutcdate()) FOR [date]
GO

Is there an option to control this behavior so that the default constraint will be unnamed after upgrade?

I am using an SMO-based approach to verify the upgrade against the model as a post-upgrade verification step. Thus far, this is the only difference I've found that I haven't been able to work around. Note that I prefer to have verification independent of the RedGate SDK since it is the SDK's actions that the verification pass is auditing...

Chris Auckland

Thanks for your post.

I don't think you can stop SQL Server automatically generating the constraint names when you create the object, but you can ask SQL Compare to ignore them during the comparison.

You can do this using Options.IgnoreConstraintNames

I hope this is heplful.

chrisdar

Thanks for your reply Chris.

The constraint that is applied in the model database somehow is scripted out with an unnamed default. So it is certainly legal in SQL (perhaps Microsoft's flavor of SQL?) to define the default in this way.

Consequently, it seems that the SQL Compare product doesn't support upgrade that maintains this construct. Can you confirm this for me with the product team?

I agree that SQL Compare concludes that these two definitions of the default constraint are equivalent. But I need to validate the upgrade independent of this product, which is why I am using SMO. While the definitions are equivalent, they aren't the same; and that's the goal of in-place database upgrade: to make the database schema the same.

Do you have a whitepaper that documents any other differences that one should expect between the model and upgraded database after the upgrade is performed?

Thanks,
Chris

Chris Auckland

Thanks for your reply.

When you create the default without defining a name, SQL Server will automatically generate the name. You can see this if you query sys.default_constraints.

When you script the object using SSMS, I believe it checks the 'is_system_named' column, and if it has a value of 1, it doesn't script the constraint name.

When a constraint is generated without an explicit name, it is very unlikely that the exact same name will be automatically generated when you create the same constraint on another database. There will almost certainly be a difference, and this is why SQL Compare gives you the ability to ignore it.

I take your point that as you have created your constraint without explicitly defining a name, SQL Compare should also be able to script the object without defining the name. I have logged a feature request in our system for this: SC-4549.

I hope this helps.