Test Results
In this section, we take a close look at SQL Refactor’s key features, testing each to see how well they perform in the real world.
Lay Out SQL
The Lay Out SQL feature is designed to take existing Transact-SQL code, and reformat it to make it more readable, based on the layout options you choose in the Options part of the program. This feature comes in especially handy when you are trying to read someone else’s code and it has not been formatted for easy reading. You can also use this feature to format the code that you write yourself if you are too lazy to format it correctly in the first place.
For Lay Out SQL to work, the code must be working code. In other words, if the code has any syntax errors, SQL Refactor will point them out to you, but it will not be able to format the code until you have fixed all the syntax errors.
You can choose to format particular sections of code, by highlighting it, or to format all the code in a query window.
Here’s a simple example of how Lay Out SQL works. Below is a simple query with no formatting.
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 WHERE ap_bank_tbl.gl_cmp_key = ‘0999288438’
If I take this same query and tell SQL Refactor to format it (using its default formatting settings) I get the following result:
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
WHERE ap_bank_tbl.gl_cmp_key = ‘0999288438’
As you can see, the second example is much more readable than the first.
What happens if there is a syntax error in the code? For example, if I remove the equals sign in the above WHERE clause, here is the message SQL Refactor gives me.
The above example was short. What happens when you ask SQL Refactor to reformat a query with hundreds of lines of code? Below is a screen shot of a stored procedure that runs 829 lines as it is currently formatted. (Obviously, not all of the code is displayed below.)
Now, I tell SQL Refactor to reformat the above code using the default formatting options.
As you can see, the previously poorly formatted code is now much easier to read. Even though there were over 800 lines of code, the entire formatting process took less than a second.
If at anytime you don’t like the formatting done for you by SQL Refactor, you can undo it by using Management Studio’s Undo option, and try again.
Of all the options provided by SQL Refactor, this option will probably be the one you use the most.
Uppercase Keywords
Like the Lay Out SQL option above, the Uppercase Keywords option is easy to use. Its purpose is to automatically uppercase Transact-SQL keywords to make code more readable.
For example, assume my code looks like the following:
Now, if I want to have SQL Refactor automatically uppercase all Transact-SQL keywords, I select the Uppercase Keywords option and I get the following results.
This is a simple example, but it works on your code no matter how long it is, making hard to read code much easier to read.
You can choose to select a portion of the code or all the code to uppercase, and if you don’t like the results, you can undo it.