Freigeben über

Best Practice: Deploying Database Rename Refactorings

  • Artikel

Team Edition for Database Professionals is targeted primarily at managing iterative changes for preproduction database development.

 

The primary use case for deploying rename refactorings is to your local sandbox server. The cleanest way to setup this sandbox server is to always drop and re-create the database at each deployment. Data can be regenerated for testing purposes using the data generation tool.

 

Sometimes, however, you will want to update an existing database and the data must be maintained. Team Data’s deployment feature does not automatically generate a build script to perform the associated data motion for you. However, this task can be accomplished through the following suggested process. The default build options result in the build script containing an add of the new table or column whenever you do a rename refactor. The default build script does not drop the old table or column. This provides you the opportunity to follow the following process to manually perform data motion using Team Edition for Database Professionals:

 

1. Perform a rename refactoring operation

a. Right-click on the object name in Schema View and select Refactor -> Rename…

2. Build the database project

a. Right-click on the database project and select Build

3. Deploy the database project

a. Right-click on the database project and select Deploy

4. Perform data motion of data from old table\column to new table\column

a. Table: INSERT INTO <new_table> (columns) SELECT (columns) FROM <old_table>

b. Column: UPDATE <table> SET <new_column> = <old_column> FROM <table> WHERE <pk_column> = <pk_column>

5. Drop the old table\column

a. Table: DROP TABLE <old_table>

b. Column: ALTER TABLE <table> DROP COLUMN <old_column>

 

Alternatively, you can avoid performing the data motion manually by finding all adds of the new table\column in the build script and converting them into sp_rename calls. The process for this would look like the following:

 

1. Perform a rename refactoring operation

a. Right-click on the object name in Schema View and select Refactor -> Rename…

2. Build the database project

a. Right-click on the database project and select Build

3. Modify the build script to replace add table\columns with sp_rename calls

a. Table: sp_rename ‘<old_table>’, ‘<new_table>’, ‘OBJECT’

b. Column: sp_rename ‘<table>.<old_column>’, ‘<new_column>’, ‘COLUMN’

4. Execute the build script

 

 

Let me know your thoughts on this guidance. Do you prefer one approach to the other? Would you do it another way?

 

Sachin Rekhi

Comments

  • Anonymous
    July 27, 2006
    The comment has been removed
  • Anonymous
    October 21, 2006
    The comment has been removed
  • Anonymous
    October 11, 2007
    I just started evaluation DB Pro. The build/deployment features were very alluring. However, I was disappointed to find that when I renamed a table, the deployment script didn't know what to do. Especially, after the fact that the tool "knew" how to update triggers, functions, and stored procedures (this is nice). This is very annoying and dangerous. I’m not if there is a tool out there that can handle this case automatically. But if there is, DB Pro should definitely have this feature. I think otherwise, the tool deployment scripts are only useful for creating blank databases, and they should have at least a warning saying that the renaming of a table would result in the creation of a new empty table and leaving the old one as it was.