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)?

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.

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:

...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.

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.

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.