ALTER COLUMN on table with DEFAULT constraint

clamk123

When creating a synchronizing script for changes to default constraints on tables, I notice an additional "unncessary" operation that is added to the script.

When I compare two identical tables, and the only difference is the addition of a DEFAULT constraint I notice the generated script contains (as an example):


...
ALTER TABLE [dbo].[tblTable] ALTER COLUMN [value] [varchar] (15)
...
ALTER TABLE [dbo].[tblTable] ADD CONSTRAINT [df_tblTable_value] DEFAULT ("hi") FOR [value]


The column definition is not different between the tables, just the constraint has been added.

We have observed this behavior with the scripts generated from SQL Compare (all versions) and also through the API I've tested regardless of settings.

In the past we've manually removed...

ALTER TABLE [dbo].[tblTable] ALTER COLUMN [value] [varchar] (15)

...as we have a lot of replication in our environments, and to issue this against the publication tables can cause schema pushes (and issues regarding clients during auditing). We also don't know if this is a legitimate column change in our source environment, or an artifact of the DEFAULT constraint that RedGate includes. We have to manually compare the source and destination to determine if it is a necessary change.

Is there a technical reason that the initial ALTER TABLE ALTER COLUMN is being added, even when the column is not changing at all, or is there a way to prevent this (other than programmatic or manual means)?

clamk123

As an extra default related operation, this only is observed if the option:

Options.ForceSyncScriptGeneration

is set.

IF we do not set that option, then ONLY the ALTER TABLE ALTER COLUMN part is generated (the unnecessary part), and the DEFAULT constraint is completely ignored. We have observed this in previous versions as well, the code to add the default constraint is not generated, until you choose to actually create the script itself (then the default constraint ALTER is added).

clamk123

Another interesting note about the "extra" ALTER TABLE, ALTER COLUMN, is that it doesn't participate in Options.ObjectExistenceChecks. If that is enabled, all the other objects receive the IF NOT EXISTS script decoration, however the initial ALTER TABLE....does NOT, which could cause an execution error.

Brian Donahue

Are you synchronizing scripts folders or a live database? If it's a folder full of scripts, there may be something peculiar about the script that is causing this.

clamk123

An excellent question, let me give you some more insight into our process.

We start from a baseline database, which we script out using RedGate into two sets of folders (tables, stored procedures, etc.). Changes are made to the files in one of the folders over time.

We then compare the new folder (b) to our original folder (a). We generate a sync file from b to a using SQL Compare.

It is in this generated file that we notice the issue. This is true of past versions of SQL Compare and the 10.0 version we're using now.

When we rip the database out into script folders, we're using whatever format that RedGate uses (for consistency so people aren't using all manner of script formats and syntax), however, I've noticed this precise situation arise regardless of the syntax as long as its valid.

What makes it more interesting, is that the SQL Compare 10.0 existence checks don't even seem to see it either, and doesn't generate the code stubs for it.

You can easily duplicate this by creating two folders (source/destination) and put in the following script in the source folder:

CREATE TABLE [dbo].[tblTable]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[value] [varchar] (15) NULL CONSTRAINT [df_tblTable_value] DEFAULT ("hi")
)
GO

and put this in the destination folder:

CREATE TABLE [dbo].[tblTable]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[value] [varchar] (15) NULL
)
GO

...then use SQL Compare 10 to compare the two foldes and generate an output script.

You'll see that you are adding the default constraint to the destination, but prior to that it does an ALTER TABLE ALTER COLUMN that just sets it to the same thing it already is. THAT is the problem we're having. 1) it doesn't participate in the "IF EXISTS" code which would generate errors if for some reason the destination didn't have the table, and 2) it triggers schema changes on the column itself, which isn't something we desire and have to manually remove it.

Try SQL Compare previous versions as well, it generates this same artifact for some reason.

Brian Donahue

Sorry, but I cannot reproduce the problem. This is the script that I get from SQL Compare:

/*
Run this script on a database with the schema represented by:

        C:\Users\Public\Documents\Database Schemas\59229b    -  This database will be modified. The scripts folder will not be modified.

to synchronize it with a database with the schema represented by:

        C:\Users\Public\Documents\Database Schemas\59229a

You are recommended to back up your database before running this script

Script created by SQL Compare version 10.1.0 from Red Gate Software Ltd at 17/04/2012 09:52:57

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
GO
PRINT N'Altering [dbo].[tblTable]'
GO
ALTER TABLE [dbo].[tblTable] ALTER COLUMN [value] [varchar] (15) COLLATE Latin1_General_CI_AS NULL
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding constraints to [dbo].[tblTable]'
GO
ALTER TABLE [dbo].[tblTable] ADD CONSTRAINT [df_tblTable_value] DEFAULT ("hi") FOR [value]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO

Is it possible that you are somehow combining two scripts in your SDK program - one deployment script from database A to B and the other from B to A?

clamk123

Your output script is showing the problem with the line:

PRINT N'Altering [dbo].[tblTable]'
GO
ALTER TABLE [dbo].[tblTable] ALTER COLUMN [value] [varchar] (15) COLLATE Latin1_General_CI_AS NULL
GO

That line is added to your output script for no reason. In order to add the default constraint to the table, there is no reason to include that line. If the data type or size had changed, then that line would be necessary to make the column alteration, but it isn't needed when just adding a default constraint, just the second part of your output script where it actually adds the constraint.

Brian Donahue

Sorry, I did not see the additional alter. There does indeed seem to be a problem, so I have logged a bug SC-5721. When it is dealt with, we should notify you.

clamk123

As an interesting observation related to this, the table alteration also seems to throw an index rebuild as well.

When I have an index on a column with a default (from the above example) and then drop just the default for comparison purposes, it also rebuilds the index and creates the below script. It drops the default (desired outcome) but also drops and recreates the index on the column, I assume because of the ALTER TABLE/ALTER COLUMN included operation.

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
PRINT N'Dropping constraints from [dbo].[tblTable]'
GO
ALTER TABLE [dbo].[tblTable] DROP CONSTRAINT [df]
GO
PRINT N'Dropping index [idx] from [dbo].[tblTable]'
GO
DROP INDEX [idx] ON [dbo].[tblTable]
GO
PRINT N'Altering [dbo].[tblTable]'
GO
ALTER TABLE [dbo].[tblTable] ALTER COLUMN [value] [varchar] (15) NULL
GO
PRINT N'Creating index [idx] on [dbo].[tblTable]'
GO
CREATE NONCLUSTERED INDEX [idx] ON [dbo].[tblTable] ([value])
GO

Brian Donahue

We may be releasing a patch to fix this early next week. Thanks for your patience.

clamk123

We look forward to it, thank you for your attention to this.