How far can development tool take you?

Published 11 October 10 07:27 PM | MartinBell

End-to-end database development is a great idea, but after a recent project that used the schema comparison tool in Visual Studio 2010 to generate upgrade scripts I had to question how feasible this actually is!

The reason I say this, is because the quality of the script generated using the schema comparison tool in VS 2010 is not always that good. Not everyone will want to totally change the way they currently work and embrace database projects in their entirety, especially if it entails a significant cost to do so. Because the schema comparison tool works to a set of pre-defined options, the code generated is usually the “safest”, but even then it is not necessarily correct and quiet often this also means that it is also the least efficient.

Here is an example, consider the following version of a given database:

CREATE DATABASE TestDB_V1
GO

USE
TestDB_V1
GO

CREATE
TABLE T1 ( id int not null PRIMARY KEY CLUSTERED,
                        longcolumnname char(1) NOT
NULL,
                        othercolumn char(1)
NULL
                  
)
GO

CREATE
DATABASE TestDB_V2
GO

USE TestDB_V2
GO

CREATE
TABLE T1 ( id int not null CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED,
                        shortercolumnname char(1) NOT
NULL,
                        othercolumn char(1) NOT
NULL
                  
)
GO

Here we have two versions of a database that contain the T1 table. Between version 1 and version 2 the column longcolumnname was renamed to shortercolumnname (sic) and the primary key was renamed from the system generated name to one that was more friendly.

If you then compare the two databases in Visual Studio 2010 you will get the following results:


image  

You may already see that there may be problems, but without knowing that the column and the PK were actually renamed how will the tool know determine the most efficient way to do the upgrade?

If you generated the script you would get the following:

PRINT N'Dropping PK__T1__3213E83F7F60ED59...';
GO
ALTER
TABLE [dbo].[T1] DROP CONSTRAINT [PK__T1__3213E83F7F60ED59];
GO
PRINT
N'Starting rebuilding table [dbo].[T1]...';
GO
/*
The column [dbo].[T1].[longcolumnname] is being dropped, data loss could occur.

The column [dbo].[T1].[shortercolumnname] on table [dbo].[T1] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue, you must add a default value to the column or mark it as allowing NULL values.
*/
GO
SET
TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
BEGIN TRANSACTION;

CREATE TABLE [dbo].[tmp_ms_xx_T1] (
    [id]                INT      NOT NULL,
    [shortercolumnname] CHAR (1) NOT NULL,
    [othercolumn]       CHAR (1) NULL
);

ALTER TABLE [dbo].[tmp_ms_xx_T1]
    ADD CONSTRAINT [tmp_ms_xx_clusteredindex_PK_T1] PRIMARY KEY CLUSTERED ([id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

IF EXISTS (SELECT TOP 1 1
          
FROM   [dbo].[T1])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_T1] ([id], [othercolumn])
        SELECT   [id],
                 [othercolumn]
        FROM     [dbo].[T1]
        ORDER BY [id] ASC;
    END

DROP TABLE [dbo].[T1];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_T1]', N'T1';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_clusteredindex_PK_T1]', N'PK_T1', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

If you allowed the option to accept data loss then you end up loosing all the data in shortercolumnname. The technique of creating a table and sucking the data across was previously used by Enterprise manager when you did this sort of thing in the GUI. There was many complaints about it, and a lot of people came to blows when something that appeared to be a simple column name rename brought systems to their knees for hours. If you now profile Management Studio when doing this operation you would see that it uses sp_rename instead:

EXEC dbo.sp_rename @objname=N'[dbo].[T1].[longcolumnname]', @newname=N'shortercolumnname', @objtype=N'COLUMN'

Therefore all the code that is used to create the new table, suck the data into it and the subsequent renames could be replaced with the single rename statement.

If you change the primary key name in Management Studio that also uses sp_rename:

EXEC sp_rename N'[dbo].[T1].[PK__T1__3213E83F7F60ED59]', N'PK_T1', N'INDEX'

So in theory there is no need to drop the existing key, which could save a significant amount of time. Unfortunately there is a downside to this. If you then tried to script the key you will get the following SQL:

ALTER TABLE [dbo].[T1] ADD PRIMARY KEY CLUSTERED (
     [id] ASC
)
WITH (PAD_INDEX
  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

i.e. the name is not scripted.

I compared the system catalogs for the two constraints in the testDB_V1 and testDB_V2 databases

SELECT *

FROM sysconstraints c
JOIN sys.objects o on c.constid = o.object_id
WHERE o.name = 'PK_T1' ;

and noted that for testDB_V1 the status value in sysconstraints is 133665 and for testDB_V2 it is 2593. Both of these values are far higher than the values documented in books online. This appears to be a “feature” so I raised an item on connect:

https://connect.microsoft.com/SQLServer/feedback/details/612575/pks-do-not-script-correctly-when-renamed-using-sp-rename#details

<Update>

20120510 - This connect item was closed with the comment fixed in next release of SQL Server. I checked SQL Server 2008 R2 SP1 and it didn't work but SQL Server 2012 RTM did. Therefore for older version use the workaround suggested by David B Howard


20101110 - An interesting comment on my connect item relating to the problem with sp_rename by David B Howard is that using OBJECT as the objtype in the sp_rename resets the is_system_named value in sys.key_constraints and can be used as a workaround.
</Update>

Back to the original reason for this blog post.. If you are going to use a tool to generate your upgrade scripts, then you should check the scripts it generates as potentially they may be inefficient and they even produce the some undesired results.

Once you start questioning or changing these scripts the use of the tool is devalued, so I don’t think it will mean the demise of the T-SQL expert just yet!!

In my next post I look at how database projects handle these two situations.

Filed under: ,

Comments

# simonsabin said on October 11, 2010 08:32 PM:

You've done something wrong. One of the plus points of VS DB projects is they understand intent and so will do a rename and not a drop and create.

How are you generating your upgrade script, are you using the build process?

# jamiet said on October 12, 2010 07:25 AM:

Did you use the Refactor-->Rename feature in Schema View? I guess not.

The tools fully support renames - if you use them properly.

# MartinBell said on October 12, 2010 10:16 AM:

I've changed the first couple of sentences to make it clear that this is about using the schema comparison tool to generate the scripts and not database projects.

# Martin Bell UK SQL Server MVP said on October 12, 2010 03:38 PM:

After looking at the code generated by the schema compare tool I decided to look at when happens in the same cicumstances when a database project is used

# Martin Bell UK SQL Server MVP said on November 22, 2010 11:06 AM:

Just because there may be problems using sp_rename to rename stored procedures, views, primary keys does it mean you should not use it do so?

This Blog

SQL Blogs

Syndication