why are alter-table script transcations like this

[dragonwell]

When I look at the script generated for making a change to a table there are some thing I don't understand about transactions. Why are there multiple BEGIN TRANSCATION and COMMIT statements? for instance

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.RouteRun
	DROP CONSTRAINT FK_RouteRun_Driver
GO
COMMIT
...etc

To me it seems like the entire script should have it's own single transaction. Even if the part of the sample above completes but then something later in the script errors, that DROP CONSTRAINT will still be committed. Right?

 

[SQLSister]

yes but if you don;t do it that way then the drop hasn't happened and it will error out in trying to make the change.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief