Archive | October, 2009

Beware the change scripts created by SQL Server Management Studio

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.

Comments are closed

Database Design Presentation

I’m extremely happy to work in an organization with an uber-smart and supportive group of developers. Once a month we meet for a lunchtime “tech talk” presentation on a topic chosen via Google Moderator.

This month, Tim Allen and I collaborated and spoke about database design. Working with Tim to research and organize the material was a blast, and even though we put silly pictures of ourselves on the slides, I’m posting them.

We focused most closely on normalization and indexing, with a few of our other best practices thrown in the mix. The concepts should be applicable to any RDBMS, but the details are specific to MS SQL Server, the database used in most of the organization’s applications.

wcit-techtalk-database-design

Comments are closed