Visual Studio Tools for Database Professionals - Rename Refactoring

I started playing again with VSTDB.

This version only includes on 'Refactoring', the 'Rename' one. The first thing I tried to do was to rename a database column.

There are 3 things to do when doing that refactoring:

  1. Renaming it in the database table. 
  2. Renaming it in every other database object that references it (stored procedures, views, etc).
  3. Renaming it in your Data Access Code

3) was obviously out of the scope of the tool, perhaps they can do it with ADO.NET v.next.

2) works OK

1) This is one seems simple but it's tricky. If you generate migration scripts by comparing two DB schemas, you never know if a column was renamed or if there's a new column and the old one was deleted. That's the first test I do with any tool that claims to do 'database refactorings'. But if the VSTDB team was doing only one refactoring for this version, at least it should be well done. But it's not. When you rename a column you get a nice warning saying "The default script that is generated at deployment time results in dropping the existing table column and adding a new table column with the new name. This can result in data loss. If data must be maintained, please perform data migration manually".

Worse, the script to do that change creates a temporary table, copies the data, and renames the temporary table with the original table name. It does not do an alter table add column/drop column.

I was disappointed.

3 Comments

  • Hey Andres,

    about not add/drop column:
    the way they implemented it is the database guy´s way of doing it :-)
    It does an implicit reorganisation of the table, so you don´t run into problems because the physical structure of the table on the disk is messed up (fragmentation). It´s about performance and that is always good.
    Also the amount of time the table is locked for applying the changes is reduced to a minimum, which is very important for productive systems.
    cheers, Michael

  • "it didn't alter the table"

    it probably would do a simple alter if you had added a column after the existing ones...

    i know Sql server doesn't support (currently) adding columns in between others... in some cases that would cause a lot of physical data movement in the pages.

    that's the reason for the temp table/data migration.

    most of the time when you add a column between others, it really doesn't matter if the column is at the end of the list or between others, so add it to the end for a less intrusive script.

  • Thanks Michael/Eric for your comments.

    I worked a little more with it. As Eric said, if I add a nullable column between two columns, it uses a temporary table, but if I add it as the last column, it does an ALTER TABLE.

    In the rename scenenario, it uses a temp table regardless of where the renamed field is.

    So, it does not look they did it because it's better for the database, as Micheal suggests (and is probably right)...

Comments have been disabled for this content.