Writing SQL Faster



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


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 Refactor and SQL Prompt products help 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.


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.


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

Refactor comes to the rescue as shown below.

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


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.


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.


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.

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


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.

CREATE PROCEDURE dbo.InsertSpecialOffer
 @category AS
varchar (50),
 @description AS varchar (255),
 @discountPercent AS smallmoney,
 @discountType AS varchar (50),
 @end AS
 @maximumQuantiy AS INT,
 @minimumQuantity AS int,
 @start AS
) AS
DECLARE @error int ;
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 

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
    DECLARE @error INT ;
    INSERT INTO Sales.SpecialOffer
    SELECT @error = @@ERROR
    IF 0 = @error
         AND 0 < @@trancount

Wrap Up


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.


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


5 (1)




5 (1)