Introduction

Programming, for a database developer, has always been a very different process to that of the procedural coder. Whereas, the world of the C# and VB programmer has been increasingly one of 'point and click' and 'Intellisense', the simpler query editors and IDEs of databases seem plain by comparison. Most experienced DBAs and Database developers will prefer it that way too. However, there has always been a demand for features and tools that in some way bridge the gap with the productivity tools available to the VB and C# programmer.

Some of the more obvious requirements come from Visual Studio and the third-party IDEs. These are...

• Intellisense, a capability introduced by Microsoft, providing context sensitive help, auto-completion of code phrases and expansion of snippets
• Preset formatting and customizable layout tools for enhancing and standardizing code readability
• Refactoring wizards, often referred to as 'lints' and 'beautifiers', for improving code quality, reusability and adherence to normalization rules through improved schema design

Various IDEs for Database Developers have been produced over the years. APEXSQL Edit, Embarcadero's Rapid SQL Pro, DTM's SQL Editor, IMCEDA's SQL IDE, and EMSs SQL Manager are amongst the many that are available, and they are usually able to present the same interface for different database systems.

However, the problem with using a third-party IDE is that it is always in 'catch-up' mode with Microsoft when it introduces new features. Even Service packs will slip new functionality into SSMS. This is why the great majority of DBAs stick to what Microsoft provides.

There is another approach. One feature that Microsoft introduced into SSMS was the ability for third-party software suppliers to add plug-in functionality into SSMS. This means that a "plug-in" tool can be integrated so closely that it becomes part of SSMS, showing its presence only by an extra menu item. Quest's Litespeed uses this feature, but Red Gate is the only company who has used the plug-in feature to enhance the productivity of the programmer.

Red-Gate's SQL Prompt product helps DBAs and database developers write SQL code faster. This article walks through a SQL Server 2005-based scenario via the SQL Server Management Studio (SSMS) IDE, demonstrating the productivity punch that tools like SQL Refactor and SQL Prompt can deliver. AdventureWorks is the sample database, as provided by Microsoft.

Writing SQL Fast

The simplified scenario starts out with a request to the AdventureWorks' DBA for two stored procedures. The first will lookup special offers; the second will add new ones to the database.

The DBA starts the exercise with a query that will return the desired columns and records for the lookup stored procedure. Immediately SQL Prompt's version of Microsoft's Intellisense starts up and searches for the right table.

The traditional approach for finding specific tables is to use sp_help, one of the rich supplies of information within SQL Server. One might, of course wish to consult an entity-relationship diagram or scroll through an object browser window. These methods work fine but take significantly longer than using SQL Prompt's auto-completion feature.

After executing the query our DBA gets a surprise. The response is an "Invalid object name 'SpecialOffer'" error message instead of a list of the special offer records. It turns out AdventureWorks leverages SQL Server 2005's richer schema feature set. Therefore, qualified object names become mandatory when an object, such as the SpecialOffer table, is neither in the dbo nor the user's default schema. This time SQL Prompt comes to the rescue as shown below.

After clicking the Qualify Object Name menu underneath the SQL Prompt main menu, the DBA successfully retrieves the records shown below.

If this simple refactoring failed to impress, consider fixing potential schema errors across 40 lines of T-SQL containing a half dozen tables! It does not take too much imagination to see the many looming parse checks and edits you're going to have to carry out.

In order to finish up the query behind the lookup procedure, the DBA still needs to specify the desired columns. For example, let us assume that the replication data (rowguid) is undesirable. SQL Prompt provides two helpful ways to choose columns. The first merely requires placing the cursor after the asterisk which generates the tool tip window shown below.

Pressing TAB replaces * with a comma delimited listing of all the column names. The second way to load columns the Column Picker - provides considerably more say in the selection process. By pressing the CTRL+LEFT keys instead of TAB the following window pops up.

Having two ways to easily load table column names speeds things up even more. If you need all columns, press the TAB key. Only need one or two columns from a wide table? Press the CTRL+LEFT keys and tick the boxes you want. Either way, writing your T-SQL script just got a lot faster.

It seems worth mentioning before moving to the next section that we could have skipped the extra work associated with setting the SpecialOffer table's schema. If you look at the above window and click the lower left arrow, it displays fully qualified names as show in the next graphic.

Refactoring

The second stored procedure, adding a special offer, involves a good deal more work compared to writing a simple SELECT. More work means more code and this will need to be laid-out and presented in an easily-readable and maintainable form before it can be placed into production. And while many of SQL Prompt's capabilities still speeds things up, it is those of SQL Refactor that come to the fore here.

Our experienced DBA starts writing the second stored procedure via the script shown below. The time has come for turning scratch work into a procedure.

use
AdventureWorks
go
DECLARE @error int, @description varchar(255), 
@discountPercent smallmoney,
@discountType varchar(50), 
@category varchar(50), @start datetime, @end datetime, 
@minimumQuantity int, @maximumQuantiy INT, 
@modified datetime
set @description = 'June Special'
set @discountPercent = 0.20
set @discountType = 'Seasonal Discount'
set @category = 'Customer'
set @start = '2004-06-01'
set @end = '2004-06-30'
set @minimumQuantity = 1
set @maximumQuantiy = NULL
set NOCOUNT ON
begin transaction
INSERT INTO Sales.SpecialOffer
  ([Description], DiscountPct, [Type], Category, StartDate, EndDate, MinQty, MaxQty)
values ( @description, @discountPercent, @discountType, @category, @start, @end,
         @minimumQuantity, @maximumQuantiy)
SELECT @error = @@ERROR
if 0 = @error AND 0 < @@trancount 
 Commit
else
 Rollback

At this point our DBA might start cutting and pasting scraps from the above into a favorite template for stored procedures. It is an easy task, but time consuming nonetheless. This code migration will likely also require a few tries to get it right. There are always those few pesky parameters refusing to be cut and pasted correctly.

Enter the "Encapsulate As Stored Procedure" wizard. It's available on the SQL Refactor drop-down menu when a code block is selected. Clicking it after selecting the code from set NOCOUNT ON to Rollback launches the wizard in the screen shot below.

After presenting a few more options to the user, SQL Refactor generates the script below. The refactoring process also replaces the extracted block with code executing the new stored procedure in the original scratch file.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.InsertSpecialOffer
(
 @category AS
varchar (50),
 @description AS varchar (255),
 @discountPercent AS smallmoney,
 @discountType AS varchar (50),
 @end AS
datetime,
 @maximumQuantiy AS INT,
 @minimumQuantity AS int,
 @start AS
datetime
) AS
BEGIN
DECLARE @error int ;
set NOCOUNT ON
 
begin transaction
 
INSERT INTO Sales.SpecialOffer
([Description], DiscountPct, [Type], Category, StartDate, EndDate, MinQty, MaxQty)
values (@description,
@discountPercent, @discountType,@category, @start, @end,
@minimumQuantity, @maximumQuantiy)
 
SELECT @error = @@ERROR
if 0 = @error AND 0 < @@trancount 
 Commit
else
 Rollback
END
 
GO

Refactoring doesn't have to end there. For example, with just a few clicks SQL Refactor can also finger unused parameters and intelligently rename objects. While our dbo.InsertSpecialOffer may not need such help, reconsider our imaginary 40 lines of T-SQL mentioned earlier. Think it might hold some dead variables or require a little name fixing? Who really wants to spend the time required for refactoring that monster into shape?

Readable Code

The ability to create instantly readable code is another feature that speeds up the process of writing SQL code. Experienced developers know that "ugly" code means hard to read, hard to debug, and hard to alter code. So we will wrap up our discussion by applying a couple of "legibility" refactorings to our not-so-pretty dbo.InsertSpecialOffer procedure.

The first of these readability improvements, and one of my personal favorites, capitalizes all keywords. Simply click the Uppercase Keywords SQL Refactor submenu. If for some reason the changes do not look right, you can just reverse them all via the standard CTRL+Z keyboard command.

But the principal step toward instant readability means adding appropriate whitespace, punctuating properly and reformatting the code with the correct indentation and line allocation. The aptly named Layout SQL feature reduces this considerable chore to a single click. Should the default styling fail to satisfy, there are over 30 customizable settings in the SQL Refactor Options submenu that mean your entire team can start coding to an agreed standard layout. They can also stick to the standard without having to constantly refer to a style guide.

Applying both the Uppercase Keywords and preset Layout SQL features reshaped our workable, albeit ugly, procedure into the professional looking, easy to read code that follows.

CREATE PROCEDURE dbo.InsertSpecialOffer
  (
    @category AS VARCHAR(50),
    @description AS VARCHAR(255),
    @discountPercent AS SMALLMONEY,
    @discountType AS VARCHAR(50),
    @end AS DATETIME,
    @maximumQuantiy AS INT,
    @minimumQuantity AS INT,
    @start AS DATETIME
   )
AS 
  BEGIN
    DECLARE @error INT ;
    SET NOCOUNT ON
    BEGIN TRANSACTION
    INSERT INTO Sales.SpecialOffer
      (
        [Description],
        DiscountPct,
        [Type],
        Category,
        StartDate,
        EndDate,
        MinQty,
        MaxQty
      )
    VALUES
      (
        @description,
        @discountPercent,
        @discountType,
        @category,
        @start,
        @end,
        @minimumQuantity,
        @maximumQuantiy
     )
    SELECT @error = @@ERROR
    IF 0 = @error
         AND 0 < @@trancount
      COMMIT
    ELSE
      ROLLBACK
    END

Wrap Up

In fairness to SQL Refactor and SQL Prompt we haven't discussed the many other cool features of these tools that further enable DBAs and database developers to write production quality code as fast as their C# and VB.NET brethren. But you can take the full products for a test drive via Red-Gate's free 14-day trial period, and kick the numbers around. The cost/benefit analysis is compelling. Most folks (and that includes me) who invest in SQL Refactor and SQL Prompt quickly improve their productivity.

And isn't that what writing code fast is all about?