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: 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 window:

1. Select an object, a column, or a parameter in the Object Explorer panel:

   1. 1.1 From the ApexSQL Refactor menu choose the Safe rename command:

      

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

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

   

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

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

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

Click the Preview button to generate the 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 Create script button. When the script is executed, ApexSQL Refactor will modify all objects’ dependencies to reflect the change.

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 panel:

1. 1.1 Under the ApexSQL Refactor menu, under sub-menu Other refactors choose the Split table command:

   

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

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

   

Set the options to create a foreign key on a primary or a secondary table and choose the Join type based on 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 Object Explorer:

   1. 1.1 From the ApexSQL Refactor, under sub-menu Other refactors select the Add surrogate key command:

      

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

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

Quick tip: ApexSQL Refactor 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.)

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:

      

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

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

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: The Relationships grid displays the list of columns that are a part of the selected relationship (this is for information only)

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 Object Explorer panel:

   1. 1.1 From the ApexSQL Refactor menu, under sub-menu Other refactors select the Change parameters command:

      

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

In the Change stored procedure parameters or the Change function parameters window parameters can be added, removed, or reordered using the Up and the Down buttons, depending on the current business needs:

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:

   

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:
   

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

   

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

   

The Summary description option at the bottom of each database refactoring window, if checked, includes the 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