Share via

Walkthrough: Apply Database Refactoring Techniques

  • Article

By using refactoring in Visual Studio Team System Database Edition, you can reduce the number of repetitive and error-prone tasks that you must perform as you design and update a database schema. For example, you can use refactoring to update references to a database object if its name must change or if the object must move to a different schema. If you take this approach, you can increase both the speed and the accuracy of routine changes to the database design.

This walkthrough illustrates a typical scenario in database development. To add capabilities to an existing database, you must perform the initial implementation and then review it with another team member. During the review, you will identify several issues that you must address before you check in the changes. You will then use a variety of refactoring techniques to change the schema.

This walkthrough illustrates the following tasks:

  • Importing your database schema

  • Implementing a typical database development task

  • Correcting a coding error

  • Completing the development task

  • Addressing code review feedback

Prerequisites

To complete this walkthrough, you need the following:

  • Database Edition.

  • Read-only access to a database server on which the AdventureWorks database is installed.

Importing Your Database Schema

Before you change a schema in a team environment, you typically check an existing project out of your version control system. For this walkthrough, you create a database project and import the schema from the AdventureWorks sample database.

To create a database project

  1. On the File menu, point to New, and then click Project.

    The New Project dialog box appears.

  2. In the Project types list, expand the Database Projects node, and click SQL Server 2005.

  3. In the Templates list, click SQL Server 2005 Database Project.

  4. In Name, type RefactorAdventureWorks, and click OK.

    A solution is created that contains an empty database project, which is named RefactorAdventureWorks, as your test (also known as a sandbox) project. No one else has access to this project when you work on it.

    Next, you import the schema from a deployed instance of the AdventureWorks database.

To import the AdventureWorks database

  1. In either Solution Explorer or Schema View, click RefactorAdventureWorks.

  2. On the Project menu, click Import Database Objects and Settings.

    Note

    You can also right-click RefactorAdventureWorks and then click Import Database Objects and Settings.

    The Import Database wizard appears.

  3. In the Source database connection list, click the connection that corresponds to the AdventureWorks database.

    Important noteImportant Note:

    If you have not yet connected to that database, you must first click New Connection to create a connection to it. For more information, see How to: Create a Database Connection.

  4. Click Start, and then click Finish when the objects and settings have been imported.

    As the schema is imported, project items that correspond to the objects in your database appear under the database project in Solution Explorer and Schema View.

    Note

    Even though you connected to the database to import the schema, you are now disconnected and working offline.

    Next, you will perform a typical task in database development, adding code to the database project.

Implementing a Typical Database Development Task

For this task, you have been asked to implement support to track the absence history of each employee. As part of this task, you must create the following objects:

  • A table that tracks the start and end date for each absence and the type of absence (vacation, sick time, jury duty, floating holiday, unpaid time off, or bereavement leave). You will add the table to the Person schema later in this walkthrough. The data in the table has the following restrictions:

    • Absences are never more than five days long. (Longer absences are broken into multiple entries.)

    • Absences have valid date ranges.

    • The table is related to the Employee table by the EmployeeID.

  • A view that shows the complete absence history of each employee.

  • A stored procedure that records an absence and updates the vacation hours for the employee if the absence type is vacation.

To prepare to add code

  1. On the View menu, click Database Schema View.

  2. In Schema View, expand the RefactorAdventureWorks node.

  3. If Schema View is sorted by object type, click Show objects by schema on the toolbar.

    Note

    Schema View is sorted by object type if it contains nodes that are named Tables and Views. If Schema View contains a node named Schemas, you can continue with the next procedure.

    Next you add the AbsenceHistory table to the database project.

To add the AbsenceHistory table

  1. In Schema View, expand the Schemas node, expand the Person sub-node, and expand the Tables sub-node.

  2. Right-click the Tables sub-node, point to Add, and click Table.

    The Add New Item dialog box appears.

  3. In Name, type AbsenceHistory, and click Add.

    The Transact-SQL (T-SQL) editor opens, displaying the definition for the AbsenceHistory table.

  4. In the T-SQL editor, replace the existing table definition with the following code:

    CREATE TABLE [Person].[AbsenceHistory]
(
[EmployeeID] INT NOT NULL, 
[BeginDate] DateTime NOT NULL,
[EndDate] DateTime NOT NULL,
[AbsenceType] NCHAR(1) NOT NULL
);

  5. On the File menu, click Save Person.AbsenceHistory.table.sql.

    Next you add a check constraint to the AbsenceHistory table.

To add the check constraint to the table

  1. In Schema View, expand the AbsenceHistory node.

  2. Right-click the Constraints node, point to Add, and click Check Constraint.

    The Add New Item dialog box appears.

  3. In Name, type CK_AbsenceHistory_ValidDates, and click Add.

    The Transact-SQL (T-SQL) editor opens, displaying the definition for the constraint.

  4. In the T-SQL editor, replace the existing constraint definition with the following code:

    ALTER TABLE [Person].[AbsenceHistory]
ADD CONSTRAINT [CK_AbsenceHistory_ValidDates] 
CHECK  (EndDate >= BeginDate AND DateDiff(days, EndDate, BeginDate) <= 5)
go
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
@value = 'Check constraint [EndDate]>= [BeginDate]', 
@level0type = N'SCHEMA', 
@level0name = N'Person', 
@level1type = N'TABLE', 
@level1name = N'AbsenceHistory', 
@level2type = N'CONSTRAINT', 
@level2name = N'CK_AbsenceHistory_ValidDates';

    This code defines a constraint on the table that makes sure that the end date is after the begin date and that the delta between them does not exceed five days.

  5. On the File menu, click Save Person.AbsenceHistory.CK_AbsenceHistory_ValidDates.chkconst.sql.

    Next you add a foreign key to the AbsenceHistory table.

To add the foreign key definition

  1. In Schema View, right-click the Keys node, point to Add, and click Foreign Key.

    The Add New Item dialog box appears.

  2. In Name, type FK_AbsenceHistory_Employee_EmployeeID, and click Add.

    The Transact-SQL (T-SQL) editor opens, displaying the definition for the foreign key.

  3. In the T-SQL editor, replace the existing definition for the foreign key with the following code:

    ALTER TABLE [Person].[AbsenceHistory] WITH NOCHECK
ADD CONSTRAINT [FK_AbsenceHistory_Employee_EmployeeID] 
FOREIGN KEY ([EmployeeID]) 
REFERENCES [HumanResources].[Employee] ([EmployeeID]) 
ON DELETE NO ACTION ON UPDATE NO ACTION;

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
    @value = 'Foreign key constraint referencing Employee.EmployeeID.', 
    @level0type = N'SCHEMA', 
    @level0name = N'Person', 
    @level1type = N'TABLE', 
    @level1name = N'AbsenceHistory', 
    @level2type = N'CONSTRAINT', 
    @level2name = N'FK_AbsenceHistory_Employee_EmployeeID';

    This code defines a foreign key relationship between the EmployeeID in the AbsenceHistory table and the EmployeeID in the [HumanResources].[Employee] table.

  4. On the File menu, click Save Person.AbsenceHistory.FK_AbsenceHistory_Employee_EmployeeID.fkey.sql.

    At this point, you realize that the table should be in the HumanResources schema instead. In the next procedure, you will correct this error.

Correcting a Coding Error

Because you have already defined constraints and foreign keys, moving a table and its related objects to a different schema would typically consume a large amount of time. You can use database refactoring to quickly and easily move the table and its related objects to the correct schema before you continue.

To move the AbsenceHistory table to the HumanResources schema

  1. In Schema View, right-click the AbsenceHistory table, point to Refactor, and click Move to Schema.

    The Move Schema dialog box appears.

  2. In the New Schema list, click HumanResources.

  3. Verify that the Preview changes check box is selected, and click OK.

    The Preview Changes dialog box appears. You can review the changes before you apply them to your database project.

  4. Click Apply.

    The refactoring changes are applied to your database project. The AbsenceHistory table moves from the Person schema to the HumanResources schema, together with all related objects.

  5. In Schema View, expand the HumanResources schema node, and then expand the Tables node.

    The AbsenceHistory table appears in the correct schema.

    Note

    When you moved the objects to the correct schema, you did not change the names of the files in which the objects are defined. If you want to update the file names, you must rename them in Solution Explorer.

    Next, you will complete the remaining steps of the development task.

Completing the Development Task

Now that you have corrected the schema for the table, you must create the following objects:

  • A view that shows, for each employee, the complete absence history.

  • A stored procedure that records an absence and updates the vacation hours for the employee if the absence type is vacation.

To add the vEmployeeAbsenceHistory view

  1. In Schema View, in the HumanResources schema, expand the Views node.

  2. Right-click the Views node, point to Add, and click View.

    The Add New Item dialog box appears.

  3. In Name, type vEmployeeAbsenceHistory, and click Add.

    The Transact-SQL (T-SQL) editor opens, displaying the definition for the view.

  4. In the T-SQL editor, replace the existing view definition with the following code:

    CREATE VIEW [HumanResources].[vEmployeeAbsenceHistory]
AS 
SELECT 
    a.* 
    ,c.[Title] 
    ,c.[FirstName] 
    ,c.[MiddleName] 
    ,c.[LastName] 
    ,c.[Suffix] 
FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Contact] c 
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [AbsenceHistory] a 
    ON e.[EmployeeID] = a.[EmployeeID] 
;

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
@value = 'Returns employee name and absence history.', 
@level0type = N'SCHEMA', 
@level0name = N'HumanResources', 
@level1type = N'VIEW', 
@level1name = N'vEmployeeAbsenceHistory';

    This code defines a view that returns data from a combination of the Employee, Contact, and AbsenceHistory tables.

  5. On the File menu, click Save HumanResources.vEmployeeAbsenceHistory.view.sql.

    Next you add a stored procedure.

To add the uspRecordAbsence stored procedure

  1. In Schema View, in the HumanResources schema, expand the Programmability node, and then expand Stored Procedures node.

  2. Right-click the Stored Procedures node, point to Add, and click Stored Procedure.

    The Add New Item dialog box appears.

  3. In Name, type uspRecordAbsence, and click Add.

    The Transact-SQL (T-SQL) editor opens, displaying the definition for the stored procedure.

  4. In the T-SQL editor, replace the existing definition for the stored procedure with the following code:

    CREATE PROCEDURE [HumanResources].[uspRecordAbsence]
@EmployeeID INT,
@AbsenceType NCHAR(1),
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN
BEGIN TRANSACTION
INSERT INTO [AbsenceHistory] (EmployeeID, BeginDate, EndDate, AbsenceType)
VALUES(@EmployeeID, @StartDate, @EndDate, @AbsenceType)
IF (@AbsenceType = 'V')
BEGIN
UPDATE [Employee]
SET [VacationHours] = [VacationHours] - DateDiff(days, @StartDate, @EndDate)
WHERE [EmployeeID] = @EmployeeID
END
COMMIT TRANSACTION
END;

    This code defines a stored procedure that adds a row in the AbsenceHistory table and updates the VacationHours field in the Employee table if the type of absence is 'V'.

  5. On the File menu, click Save dbo.uspRecordAbsence.proc.sql.

    In the next procedure, you address feedback that you receive in a code review.

Addressing Code Review Feedback

When you reviewed your code with another member of the team, you received feedback about several best practices. You were asked to avoid the use of SELECT *, because it produces warnings if you run static code analysis on your database code. In addition, you were asked to fully qualify the names that you used in the stored procedure. Finally, you were asked to rename the BeginDate column in the AbsenceHistory table to StartDate.

Note

Coding standards and requirements vary between teams. You should apply the coding standards for your organization to the T-SQL code that you write. This walkthrough is illustrating two issues. In addition, you would typically apply these techniques to all new code (for example, fully qualifying all the names in the new code), not just to a single database object.

Again, these types of changes can be tedious and error-prone to implement. You can use database refactoring to quickly and easily update your database code, test code, and data generation plans.

To expand the SELECT * in the view definition

  1. In Schema View, double-click the vEmployeeAbsenceHistory view.

    The T-SQL editor opens, displaying the definition of the view.

  2. On the Data menu, point to Refactor, and click Expand Wildcards.

    The Preview Changes dialog box appears.

  3. In the Expand Wildcards list, click a.*.

    The Preview Changes pane shows the updates that will be applied to the view.

  4. Click Apply.

    The changes are applied to your database project. Next you will fully qualify the names in the stored procedure that you defined in an earlier procedure in this walkthrough.

To fully qualify the names in the stored procedure

  1. In Schema View, double-click the uspRecordAbsence stored procedure.

    The T-SQL editor opens, displaying the definition of the stored procedure.

  2. On the Data menu, point to Refactor, and click Fully-qualify Names.

    The Preview Changes dialog box appears and displays all the changes that will be made if you apply the refactoring operation to your project.

  3. After you review the changes, click Apply.

    The changes are applied to your database project.

To rename the BeginDate column

  1. In Schema View, expand the AbsenceHistory table, expand the Columns node, click the BeginDate column.

  2. On the Data menu, point to Refactor, and then click Rename.

    The Rename dialog box appears.

    Note

    You can also right-click BeginDate in Schema View, point to Refactor, and then click Rename.

  3. In New name, type StartDate.

  4. Select the Preview changes check box, and click OK.

    The Preview Changes dialog box appears and displays all the changes that will be made if you apply the rename operation to your database project.

  5. Click Apply.

    The changes are made. The column name is updated, and the new column name appears in Schema View for each object that was updated. If you open the definition for the date constraint that you specified earlier in this topic, the constraint has also been updated to reference the new column name.

Next Steps

At this point, you would typically review the updates with the team member that performed the code review, and then you would check in your changes to version control. At this point, you have updated your database project, the offline representation of your database schema. You must deploy that database project to a target database to update the deployed schema.

When you apply a refactoring operation to your database project, information about that operation is recorded in a refactoring log file if you can rename or move the object by using sp_rename or ALTER. In this walkthrough, the log file is named RefactorAdventureWorks.refactorlog. The refactoring log file is used at deployment time to try to preserve the intent of your refactoring changes. For example, the refactoring log will record your changes if you rename a column. At deployment time, that information prevents the column with the old name from being dropped, along with any data it contains, and an empty column being created with a new name. If you use refactoring, you do not have to add statements to the pre-deployment and post-deployment scripts to preserve the data.

See Also

Tasks

How to: Deploy Database Refactoring Changes

Concepts

Move a Database Object to a Different Schema

Fully Qualify the Names of Database Objects

Expand Wildcard Characters in SELECT Statements

Improving Database Code with Static Analysis

Other Resources

Walkthrough: Deploying Database Refactoring Changes