Best Practice: Deploying Database Rename Refactorings
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