ApexSQL Refactor vs RedGate® SQL Prompt – Part 2 – Additional refactors

Some SQL refactors in RedGate® SQL Prompt and ApexSQL Refactor are similar and some are different. In a previous article, the main differences in SQL refactors supported by ApexSQL Refactor and RedGate® SQL Prompt were shown.

ApexSQL Refactor offers additional database refactors not supported by RedGate® SQL Prompt and these additional refactors will be described in this article.

The Add surrogate key option

A surrogate key is a generated unique value that is used as the primary key of a database object. Surrogate keys are considered when the natural key consists of many columns, is very long, or may need to change. When changing a table’s key in a deployed database, it is important to update all of the table’s dependencies accordingly.

To safely change a key using ApexSQL Refactor, select a table in the SSMS Object explorer and from the ApexSQL Refactor menu choose the Add surrogate key option:

Enter the name of the column which will be created on the table as a surrogate key and click the Generate preview button:

The Change parameters option

In order to change the statements or parameters in a stored procedure or a function, either delete and re-create a stored procedure or a function or use the ALTER statement. To safely change parameters using ApexSQL Refactor, select a table in the SSMS Object explorer and from the ApexSQL Refactor menu choose the Change parameters option.

Using the Change parameters option add, remove, or change a parameter for a function or a procedure without breaking the database integrity and without taking care about references that may have been broken:

The Replace one-to-many relationship option

In database design, a one-to-many relationship is a relationship between two entities when one entity has a multivalued relationship with another entity. For example a department can have many employees, but each employee can work for only one department. In some cases when additional requests are added to a database design the relationship must be changed. For example, adding a review for every employee. Using the junction table is one approach. A junction table is a table that contains common fields from two or more tables within the same database and is on the “many” side of a one-to-many relationship with each of the other tables.

Using ApexSQL Refactor one-to-many relationship can be changed with an association table easily. Select the table in the Object Explorer and from the ApexSQL Refactor menu choose the Replace one-to-many Relationship option:

The Copy code as option

Using ApexSQL Refactor’s the Copy code as option, convert the selected SQL code into a chosen client programing language without manually adding code at the beginning and the end of every SQL statement.

Select code in the query window and from the ApexSQL Refactor menu, choose the Copy SQL code as option and one of the languages listed in the submenu:

Copy code as C# gives:

string sql = "CREATE TABLE [Person].[Password](\n"
           + "	[BusinessEntityID] [int] NOT NULL,\n"
           + "	[PasswordHash] [varchar](128) NOT NULL,\n"
           + "	[PasswordSalt] [varchar](10) NOT NULL,\n"
           + "	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, \n"
           + "	[ModifiedDate] [datetime] NOT NULL, \n"
           + ") ON [PRIMARY];";

Languages supported by default are Java, VB.NET, C#, Perl, PHP, Delphi, Power Builder, Ruby, and C++. To add new syntax, create a custom language template by choosing the Customize languages option:

1. From the ApexSQL Refactor menu, choose the Copy code as option, then the Customize languages option:

   

2. When the Customize language templates dialog is shown, click the New button

3. Here the following can be specified: a name for the language template, escape characters for quotes, code that will be added before and after SQL statements, and the characters that end a line, so SQL statements can be correctly parsed by the client code language:

   

4. The new language will appear in the Copy code as menu:

   

See also:

ApexSQL Refactor vs RedGate® SQL Prompt Refactors – Part 1 – Common refactors compared “Encapsulate As”
ApexSQL Refactor vs RedGate® SQL Prompt Formatting – Part 3 – Common formatting options compared
ApexSQL Refactor vs. RedGate® SQL Prompt – Part 4 – Additional formatting options
ApexSQL Refactor vs. RedGate® SQL Prompt – Part 5 – Additional formatting features

Disclaimer: All product and company names are trademarks™ or registered® trademarks of their respective holders. ApexSQL is not affiliated, associated, authorized, endorsed by, or in any way officially connected with them or any of its subsidiaries or its affiliates