Red Gate's SQL Refactor Boosts DBA Productivity

Qualify Object Names

One of the simple things you can do in SQL Server to help boost performance and to make code more readable is to qualify all objects with object owners (SQL Server 2000) or schema (SQL Server 2005). A lot of developers are not familiar with why objects should be qualified, and because of this, don’t do it.

If you run across examples of code that reference unqualified objects, you can have SQL Refactor add one level of object qualification for you. If you are using SQL Server 2000, or a SQL Server 2005 database in 2000 mode, then SQL Refactor can figure out what the first level object owner qualification is and automatically add it to all object names in a script. If you are using SQL Server 2005 databases running in 2005 mode, then SQL Refactor can add a schema qualification to all objects in a script.

Let’s look at a simple example. Below is the FROM and WHERE clause of a query that sometimes uses object qualification and sometimes does not.

After running SQL Refactor, we get the following result.

As you can see, it filled in the DBO object owner for those objects that were not qualified before. Although not shown here, SQL Refactor can also take column names and qualify them with their table name.

This feature can be performed on partial or complete scripts, and unlike all of the SQL Refactor features tested so far, takes a little longer to run. This is because SQL Refactor must connect to the database and look up the data it needs to insert into your script. While there is short delay when this happens, it is still very quick, and is finished within seconds.

Expand Wildcards

We all have seen code that uses SELECT * instead of specifying all of the columns to be returned in a query. And we all know why we see this. It’s because whoever wrote the code was lazy. We also know that this is a very poor practice because it makes scripts harder to read, can cause debugging problems, and it can also end up returning more data that you need, which hurts the query’s performance.

SQL Refactor takes away our reason for being lazy, and can automatically expand a SELECT * into the needed columns, all fully qualified with the table name. Here’s an example of how it works.

Let’s say we have the following query:

SELECT * FROM dbo.ap_bank_tbl

Unless we know the table structure, we don’t know what columns are being returned. So if we want to expand this query manually to show qualified column names, we would have to view the table’s properties and then type in a lot of information, which is really boring. Or, if we let SQL Refactor do the work, in a matter of seconds, our query can be written as:

SELECT ap_bank_tbl.gl_cmp_key,
     ap_bank_tbl.ap_bank_key,
     ap_bank_tbl.ap_bank_acctno,
     ap_bank_tbl.ap_bank_upcnt,
     ap_bank_tbl.ap_bank_desc,
     ap_bank_tbl.ap_bank_cash,
     ap_bank_tbl.ar_bank_recpt,
     ap_bank_tbl.ar_bank_depst,
     ap_bank_tbl.ap_bank_entdt,
     ap_bank_tbl.ap_bank_lckno,
     ap_bank_tbl.ap_bank_novch,
     ap_bank_tbl.sa_user_key,
     ap_bank_tbl.ap_bank_chgdt,
     ap_bank_tbl.ap_bank_crtdt,
     ap_bank_tbl.ap_bank_inuse,
     ap_bank_tbl.ap_bank_routno,
     ap_bank_tbl.gl_crncy_key,
     ap_bank_tbl.ap_bank_curf,
     ap_bank_tbl.ap_bank_recptint,
     ap_bank_tbl.gl_bkcd_key
FROM  dbo.ap_bank_tbl

As you can see, this saved a lot of typing (and the inevitable wasted time looking for typing errors).

To perform this task, SQL Refactor connects to the database and pulls the table schema in order to expand out all the columns in the table. This is handy if we are writing new code, or making older code more readable.

One thing to keep in mind when using this feature is that all the columns of a table are displayed, which is what is required to properly represent SELECT *. But keep in mind that in most cases, you don’t need to return all rows, just the number of columns to meet you needs. This means that you need to review the expanded SELECT statement and remove unnecessary columns so unneeded data is not returned.

This feature can be performed on partial or complete scripts, and may take several seconds to run as it connects to the database to identify the needed table schema.

Encapsulate as New Stored Procedure

SQL Refactor has the ability to take a selection of Transact-SQL code and turn it into a stored procedure. If this selection of code is part of a larger collection of code, you can have SQL Refactor create the new stored procedure and then replace the original selected code with a call to the newly created stored procedure.

Below is a simple example of how this works. In this simple example, we see a script that has one variable. Let’s say we want to turn this into a stored procedure using SQL Refactor.

To accomplish this task, you first select the code (with the exception of the DECLARE and SET statements), and then tell SQL Refactor to Encapsulate the Code Into a Stored Procedure. When you do this, you see this screen.

In the above screen you assign the new stored procedure an owner and a name. Notice, that SQL Refactor has identified the single parameter for the stored procedure. Once you are happy with the name and owner, you click Next and you see the following screen.

With this screen, you decide if you want SQL Refactor to replace the selected code with a call to the new stored procedure, or not. The screen shows you what the call would look like. You make your choice and then click View Script. This will cause a script to create the stored procedure appear in a new query window, which you can run if you like.

I am not sure how often you would use this feature as most developers will probably write stored procedures from scratch, not create a larger script and then decide later to encapsulate part of it into a stored procedure. In addition, even if this were true, it is almost just as easy to create the stored procedure manually as it is to use SQL Refactor to do the work for you.

Continues…

Leave a comment

Your email address will not be published.