ALTER COLUMN on table with DEFAULT constraint

clamk123clamk123 Posts: 40
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)?

Comments

  • 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).
  • 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 DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • 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 DonahueBrian Donahue Posts: 6,590 Bronze 1
    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?
  • 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 DonahueBrian Donahue Posts: 6,590 Bronze 1
    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.
  • 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 DonahueBrian Donahue Posts: 6,590 Bronze 1
    We may be releasing a patch to fix this early next week. Thanks for your patience.
  • We look forward to it, thank you for your attention to this.
Sign In or Register to comment.