How to refactor SQL databases with ApexSQL Refactor

Applies to
ApexSQL Refactor

Summary
This article describes ApexSQL Refactor database refactoring features.

Table of contents:

  1. Safe rename SQL objects, columns, and parameters
  2. Splitting a table
  3. Add surrogate key
  4. Change parameters
  5. Replace-one-to many relationship
  6. Common options

Description

Safe rename SQL objects, columns, and parameters

ApexSQL Refactor offers the Safe rename feature for renaming SQL database objects, columns, and procedure and function parameters.

Quick tip icon

Quick tip:

Use the Safe rename feature to increase readability, align database naming conventions, or to clarify the meaning of an object

There are three ways to open the Safe rename dialog:

  1. Select an object, a column, or a parameter in the Object Explorer pane:
    1. 1.1 From the ApexSQL Refactor menu choose the Safe rename command:

      Choose the Safe rename command from the ApexSQL Refactor menu

    2. 1.2 Use the “Ctrl + Shift + Alt +A” shortcut.

  2. Right click an object, a column, or a parameter in the Object Explorer pane and select ApexSQL Refactor->Safe rename command:

    Choosing the Safe rename command in the Object Explorer pane

The Safe rename dialog also offers an option to change the object’s schema:

Figure illustrating options to change the object’s schema

In the New name textbox enter a new name of an object:

Entering a new name of an object using the New name textbox

ApexSQL Refactor checks for duplicate names. If an object with a specified schema and name already exists ApexSQL Refactor will prompt a warning message:

Warning message shown if an object with a specified schema and name already exists

Click the Preview button to generate the T-SQL refactoring script in the Preview section:

Figure illustrating the generated T-SQL refactoring script in the Preview section

Clicking the Open button will open the generated script in the query editor.

To execute the script click the Rename button. When the script is executed ApexSQL Refactor will modify all objects’ dependencies to reflect the change.

Quick tip icon

Quick tip:

Renaming an object using the SQL Server Management Studio Rename feature or the sp_rename stored procedure will not update objects that reference the renamed object by its original name and objects definition will contain the its original name

Back to top

Splitting a table

The most common reasons for splitting a table in two separate tables are:
Performance: in cases where most of the queries retrieve all columns from a table that contains several very wide text or BLOB columns to reduce access times the BLOB columns are split to its own table.

Security: splitting data to a separate table restrict access to sensitive data e.g. login or salary information etc.

There are two ways to split a table in ApexSQL Refactor:

  1. Select a table in the Object Explorer pane:
    1. 1.1 Under the ApexSQL Refactor menu, under sub-menu Other refactors choose the Split table command:

      Choosing the Split table command under the ApexSQL Refactor menu

    2. 2. 1.2 Use the “Ctrl + Shift + Alt +O” shortcut

  2. In the Split table dialog set the new schema and enter the name for a new table:

    Setting the new schema and entering the name for a new table in the Split table dialog

Set the options to create a foreign key on a primary or a secondary table and choose the JOIN type based on your needs.

Clicking the Create script button will open the generated script in the query editor. To execute the generated script, click the Execute button.

Back to top

Replacing a natural primary key with a surrogate

Common reasons for replacing a natural primary key with a surrogate key:

To reduce coupling: since a surrogate key it doesn’t have a business value and it’s not coupled with any external application connected to a database it won’t be updated if the business logic changes.

To increase performance: a large composite natural key can degrade database performance, and surrogate keys are usually integer values therefore a smaller index on a primary key will have better performance on JOIN operation and reduce the complexity of JOIN operations.

There are two ways to replace a natural key with a surrogate in ApexSQL Refactor:

  1. Select a table in the Object Explorer:
    1. 1.1 From the ApexSQL Refactor, under sub-menu Other refactors select the Add surrogate key command:

      Selecting the Add surrogate key command from the ApexSQL Refactor menu

    2. 1.2 Use the “Ctrl + Shift + Alt +K” shortcut

  2. In the Add surrogate key dialog enter the name for the column which will be created as a surrogate key in the Surrogate column name textbox.

Quick tip icon

Quick tip:

ApexSQL Rector sets the surrogate column name by default as <Table_name> + “ID”. If a column with the same name already exists, provide a unique name for a surrogate key column (i.e. <Table_name> + “ID2”, etc.)

Entering the name for the column in the Add surrogate key dialog

Clicking the Create script button will open the generated script in the query editor. To execute the generated script, click the Execute button.

Back to top

Replacing one-to-many relationship with an associate table

The Replace one-to-many relationship database refactoring feature makes use of an associative table approach to isolate the existing relationship between the “One” and “Many” side in a separate associative table.

There are two ways to replace a one-to-many association with an associative table in ApexSQL Refactor:

  1. Select a table in the Object Explorer:
    1. 1.1 From the ApexSQL Refactor menu, under sub-menu Other refactors select the Replace one-to-many relationship command:

      Selecting the Replace one- to- many relationship command from the ApexSQL Refactor menu

    2. 1.2 Use the “Ctrl + Shift + Alt + E” shortcut

In the Replace one-to-many relationship with associative table dialog:

  1. From the Dependent table drop-down list select a dependent table
  2. Select relationships for the selected dependent table from the Relationship drop-down list
  3. In the Associative table name specify the name for a new associative table that will be created
Quick tip icon

Quick tip:

The Relationships grid displays the list of columns that are a part of the selected relationship (this is for information only)

Figure showing the Replace one-to-many relationship with associative table dialog

Clicking the Create script button will open the generated script in the query editor. To execute the generated script click the Execute button.

Back to top

Change procedure and function parameters

There are two ways to change a stored procedure and function parameters in ApexSQL Refactor:

  1. Select a table in the Object Explorer:
    1. 1.1 From the ApexSQL Refactor menu, under sub-menu Other refactors select the Change parameters command:

      Selecting the Change parameters command from the ApexSQL Refactor menu

    2. 1.2 Use the “Ctrl + Shift + Alt + Z” shortcut

In the Change stored procedure parameters or the Change function parameters dialog you can add, remove, or reorder parameters using the Up and the Down buttons, depending on the current business needs:

Figure illustrating the Change function parameters dialog

The Signature tab displays the name of an object and its parameters.

Clicking the Create script button will open the generated script in the query editor. To execute the generated script click the Execute button.

Back to top

Common options for the Safe rename, the Change parameters, the Replace one-to-many, the Split table, and the Add surrogate key database refactoring features

The Preview section contains four tabs:

  1. ApexSQL Refactor creates an update script that allows performing all available database refactoring methods without breaking dependencies. The Generated script tab shows a complete refactoring SQL script:

    Figure showing the Generated script tab

  2. The Warnings tab displays all referenced objects that require a user’s analysis before running the script and any potential problems caused by the refactoring. Some warning examples:

    The Warnings tab - warning example 1

    The Warnings tab - warning example 2

  3. The Sequence tab shows the sequence of operations to be done:

    The Sequence tab

  4. The Dependencies tab displays a list of the objects that need be altered to maintain all renamed object/column/parameter dependencies:

    The Dependencies tab

The Include summary description option at the bottom of each database refactoring dialog, if checked, includes the actions and warnings at the beginning of the generated SQL script:

Summary dialog showing actions and warnings at the beginning of the generated SQL script

Back to top

Useful resources:
Walkthrough: Apply Database Refactoring Techniques
Partitioning
Understanding SQL Dependencies