Transaction usage in SQL Source control is bad.

We have the problem that if you could fetch and apply one migration script at a time, you can be okay, but when you are three or four updates behind, redgate sql source control has the following rules, which I believe are stupid:

1. begin transaction like this:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
GO

2. do seven million lines of scripted changes.
3. at the end, commit or do not commit like this:

IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed, restore from backup'
END
GO

SQL Server can not scale the above approach out to actually make data and schema migrations work, even in 5.0.
Suppose we have the following sequence of changes:

A. We alter table T1, adding column T1C10
B. Now we insert data into T1C10, before the DML schema change is committed.
C. Now we get an error that you can't put data in some field in the table that is pending commit. The errors SQL Server shows are like this:

Msg 207, Level 16, State 1, Line 1261
Invalid column name 'T1C10'.

The product seems fundamentally designed badly in that it is unaware of these impossibilities and simply passes them along to me as an end user. My desire while using the product is to just update my database.

It seems to me that you could analyze the sequence of changes and build a sensible set of transactions within a migration, even when multiple new style 5.0 migration scripts are being fetched and applied in one batch.

This "one transaction" model is lame and has to be fixed.

In a discussion with a support person someone mentioned there are configurable "Comparison Options" but I can't find that anywhere in the GUI in RedGate SQL Source Control 5.1.1.2694. Where is that comparison option now?



Warren