Part of my job is moonlighting as a SQL Server database admin, so a co-worker recently asked me to run a script against a production table. The task was simple: add a few new columns and create some indexes. He had generated the script by using SQL Server 2008 Management Studio’s (SSMS) Generate Change Script function.
Although the general sequence of steps in the generated script made sense, I have some complaints about the SSMS output. Here’s a very simple recreation of the scenario.
Consider a table with two columns. One is a primary key, and one has a default constraint:
1 2 3 4 5 6
CREATE TABLE phillies ( phils_id INT IDENTITY(1,1) , phils_year CHAR(4) NOT NULL CONSTRAINT df_phillies_year DEFAULT (2008) , CONSTRAINT pk_phils_id PRIMARY KEY CLUSTERED (phils_id))
Using the SSMS table design view to add two columns to the phillies table and saving the change script results in the following code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
BEGIN TRANSACTION GO ALTER TABLE dbo.phillies DROP CONSTRAINT df_phillies_year GO CREATE TABLE dbo.Tmp_phillies ( phils_id INT NOT NULL IDENTITY (1, 1), phils_year CHAR(4) NOT NULL, division_champ_flag bit NOT NULL, national_champ_flag bit NOT NULL ) ON [PRIMARY] GO ALTER TABLE dbo.Tmp_phillies ADD CONSTRAINT df_phillies_year DEFAULT ((2008)) FOR phils_year GO SET IDENTITY_INSERT dbo.Tmp_phillies ON GO IF EXISTS(SELECT * FROM dbo.phillies) EXEC('INSERT INTO dbo.Tmp_phillies (phils_id, phils_year) SELECT phils_id, phils_year FROM dbo.phillies WITH (HOLDLOCK TABLOCKX)') GO SET IDENTITY_INSERT dbo.Tmp_phillies OFF GO DROP TABLE dbo.phillies GO EXECUTE sp_rename N'dbo.Tmp_phillies', N'phillies', 'OBJECT' GO ALTER TABLE dbo.phillies ADD CONSTRAINT pk_phils_id PRIMARY KEY CLUSTERED (phils_id) ON [PRIMARY] GO COMMIT
This script contains BEGIN TRANSACTION and COMMIT TRANSACTION statements but doesn’t accompany them with any kind of error handling. So if you run it as-is and encounter an error, nothing gets rolled back.
Any error handling that you might add, however, is thwarted by the fact that the script’s statements are contained in individual batches (i.e., separated by GO statements).
Say you individually check each statement for errors and issue a rollback/return if something goes awry.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
BEGIN TRANSACTION GO [snip] CREATE TABLE dbo.Tmp_phillies ( phils_id INT NOT NULL IDENTITY (1, 1), phils_year CHAR(4) NOT NULL, division_champ_flag bit NOT NULL, national_champ_flag bit NOT NULL ) ON [PRIMARY] GO [snip] -- throw an error SELECT 23/0 IF @@ERROR <> 0 BEGIN PRINT 'error!' ROLLBACK TRANSACTION RETURN END [snip] GO DROP TABLE dbo.phillies
In this scenario, changes that occurred prior to the error will be rolled back. However, although the RETURN statement exits the current batch, subsequent batches (for example, the one that deletes your table) will execute.
A TRY/CATCH block is another potential error-handling method, but TRY/CATCH blocks can’t span multiple batches.
So what’s with the batch-happy SSMS? In older versions of SQL it might have been necessary to separate a CREATE TABLE and subsequent ALTER TABLE statements into separate batches (I haven’t tested this). But SQL 2005 and 2008 provide statement-level recompilation, so the multitude of batches is not necessary.
When I tested a “GO-less” version of the script, it worked swimmingly. It’s understandable that SSMS can’t generate the error-handling, but if it would reduce the scripts’ number of batches to the minimum required, it would be easier to add the error handling yourself.
Conclusion: don’t rely on these SQL Server Management Studio-generated change scripts for any serious work, and definitely don’t use them as a model for how to write robust T-SQL.