Red Gate's SQL Refactor Boosts DBA Productivity

Product: SQL Refactor (Version 1.0)

Publisher: Red Gate Software Limited

Pricing:

• Single User License: $295
• Quantity and bundle discounts available

Key Features and Benefits:

• Reformats Transact-SQL code to make it more readable.
• Uppercases Transact-SQL keywords to make code more readable.
• Script summary feature provides a high-level overview of code.
• Identifies unused variables and parameters in code that can be removed.
• Qualified object names can be automatically added to object references that currently don't have them.
• SELECT * code can be expanded to list column names.
• Transact-SQL scripts can be automatically encapsulated into a stored procedure.
• Objects, column names, and parameter names can be renamed, along with all references to the renamed objects.
• A single table can be split into two tables, including data.

Introduction

As a DBA who spends most of his time performing administrative tasks, I don't do a lot of Transact-SQL code development. On the other hand, I get lots of opportunities to read Transact-SQL code, especially when I am tasked with figuring out how to boost the performance of a slow running query or stored procedure.

I have spent many an hour trying to wade through Transact-SQL code, figuring out what the code is trying to do. In almost every case, the code is poorly formatted, making it that much more difficult to read and understand. This is especially true if the code runs 1,000 lines or more. In fact, a couple of times I have thrown my hands up and given up trying to read some code.

I guess I am not the only person who has this problem because the developers at Red Gate Software have released SQL Refactor, a SQL Server 2005 Management Studio utility that, among its many handy features, is designed to take poorly formatted Transact-SQL code and make it more readable.

In this review, we are going to take an in-depth look at SQL Refactor and learn how it can make our lives, as a DBA or Transact-SQL developer a little easier (in some cases, a lot easier).

Review Roadmap

In this review, here is what we are going to look at:

• What are SQL Refactor's key features, and do they perform as expected?
• How is it architected?
• How does it work?
• Is it easy to install and administer?
• How does it affect performance on production servers?
• Does it meet the needs of the typical DBA?

All of these questions will be answered in this extensive review.

Features Tested

SQL Refactor's features are very specific and easy to define. These features can be used individually, or in any combination to best meet your needs. They include:

• Reformats Transact-SQL code to make it more readable.
• Keyword uppercasing automatically uppercases Transact-SQL keywords to make code more readable.
• Script summary feature provides a high-level overview of code, making it easier to understand complex code written by others.
• Identifies unused and unneeded variables and parameters in Transact-SQL code that can be removed to make code easier to understand.
• Qualified object names can be automatically added to object references in code that currently doesn't have them.
• SELECT * code can be automatically expanded to list the actual column names for better readability.
• Transact-SQL scripts can be automatically encapsulated into a stored procedure, including input and output parameters.
• Objects, column names, and parameter names can be renamed, along with all references to the renamed objects.
• A single table can be split into two tables, including data. In addition, code is automatically modified to work with the new tables.

We will be examining each of these features in this in-depth review.

Before we look at each of the above features, let's learn a little more about how SQL Refactor works.

Architecture

SQL Refactor has been written as a SQL Server 2005 Management Studio add-in. Because of this, there is no separate GUI. After installing SQL Refactor, when you bring up Management Studio, you see a new menu option at the top of Management Studio called "SQL Refactor." In addition, depending on your current context inside Management Studio, SQL Refactor functionality can be invoked by right-clicking on an object.

SQL Refactor is designed to be installed on each DBA or developer workstation where Management Studio is currently being run.

Installation

In this section, we take a quick look at how SQL Refactor is installed.

SQL Refactor Requirements

To run SQL Refactor, the following requirements must be met:

• Windows 2000 Workstation or Server, Windows 2003 Server, or Windows XP

• SQL Server 2005 client-side tools
• SQL Server 2005 Management Studio
• Microsoft .NET Framework 2.0
• MDAC 2.8+
• 128 MB RAM
• 10 MB hard disk space

SQL Server Versions Supported

• SQL Server 2005
• SQL Server 2000 (when managed from Management Studio)

Installing SQL Refactor

Installing SQL Refactor is easy and quick. Once all of the prerequisite software has been installed, start the SQL Refactor setup program. After going through the obligatory introduction and license agreement screens, you can specify where you want SQL Refactor physically installed, and it is installed in less than a minute.

To see and use SQL Refactor, you must start Management Studio. At some point after installing the software, you will need to enter the Product Key. Once the key has been entered and the product activated, it is ready to use. If you don't have a key handy, you can still use the product without a key for 14 days.

Periodically, if you like, you can also ask SQL Refactor to check for the newest updates, which is a handy feature.