This is a showcase review for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.
Introduction
This article describes how you can use SQL Refactor to sharpen up your T-SQL. Click here for your free trial.
Case #1: The New Kid - A new guy has just started work as a database developer at your office. He did some SQL coding at college and again during a summer internship at his uncle�s hardware store. The trouble is, he hasn�t a clue about coding standards or how to format a query so it�s readable at a glance.
Case #2: The Old Database - You�ve just landed the job you always wanted, Chief DBA at the transnational corp down the road from where you live. Now you learn there�s not, as agreed, going to be a handover period. Your predecessor � the guy who designed most of the databases � has upped and moved to Hawaii. And you can see exactly why he�s fled.
Case #3: The Merger - After 6 months of preparatory work your company has just merged with a significant competitor and you�re part of the team with the task of rationalising the databases across the new corporate structure. On the one hand you need structural simplicity � you don�t want customers in disparate databases � and on the other hand, you�ve just taken on board a much higher granularity of customer information.
In all of these cases - and in plenty of others � the overriding priority is to make order out of chaos. Establishing robust manageability of a complex database installation, plus a team of diverse SQL talents, is a daunting task. Your goals are simplicity, legibility, reusability, efficiency and above all security.
Imagine a future when SQL Server Management Studio has on-board tools that ensure all of the following:
- all members of your team automatically code to the same standards, no matter how recently they were hired
- automatic, intelligent object renaming with all dependent references
- instant summarization of long and complex scripts or queries
- encapsulation of script portions as separate stored procedures
- one-click uppercasing of keywords
- totally customizable SQL layout settings
- the ability to split a table into two tables and maintain full referential integrity
Now imagine this future version of Management Studio (which performs equally well with SQL Server 2000 as with SQL Server 2005) is available right now.
Red Gate Software has just released a totally new productivity tool, SQL Refactor, which installs directly onto the toolbar of Management Studio.
It enables you to perform over a dozen powerful transforms on your T-SQL code with zero performance impact. In this article we�ll just look briefly at a few of them.
�I think this is an indispensable tool and I wish I�d had it several months ago when I was completely rewriting a hundred stored procs.�
Andrew J Kelly, MVP, SolidQualityLearning.com.
Custom SQL Layout - It may be because of a new addition to the team, or it may be a legacy database, but either way, you�re faced with a script that is hard to decipher or doesn�t fit with your company coding practices. Something perhaps not quite as insane as this below but we can all recognise birds� nest soup:
Fortunately, if you have SQL Refactor installed, then you can simply drop open the menu and apply �SQL Layout� to format the script so it is immediately legible.
A further refactoring �Uppercase Keywords� will do exactly what it says, either to the whole script or to a selected portion of the script.
Moving down the list of toolbar menu items, if you are analyzing a large and complex SQL script from a legacy database, it is often less than simple to decipher quickly what the detailed actions of the script are. The �Summarize Script� refactoring will provide an easy-to-read summary of any script, no matter how complex (see diagram left). Clicking on a keyword in the Summary Script will directly highlight and move your cursor to the corresponding keyword in the full script open in the query window.
Smart Rename
|
|
There are also two really powerful refactorings which are not about scripting but about rationalizing the structure of the database. These two are therefore context sensitive, i.e. they become available at the appropriate place and time in the object explorer. For example, right-clicking on a table name in the object explorer will bring up the usual scripting menu items, but with two additional items listed, Smart Rename and Table Split (see diagram left).
The native �Rename� menu item in Management Studio is the same as �Rename� in any Microsoft application. It simply allows you to rename the selected object with no question asked. But if you�re working on a database of even medium complexity, this is clearly of limited utility.
SQL Refactor�s �Smart Rename� refactoring however, identifies and updates all dependent references to the object you need to rename. |
It generates a script that will update all those references without you having to go and find them and update them by hand. The Smart Rename wizard also provides an Action Plan (listing all the steps in the creation SQL script), a Warnings tab and a list of all the Referencing Objects that will be updated by carrying out the rename.
Table Split
This is another extremely powerful, context-sensitive structural refactoring. Although not the sort of thing one would carry out on a daily or even weekly basis, when it becomes urgent to do, then having a 4-click means of carrying out can save days of work. In most companies a common side-effect of volume growth is that the quantity and type of customer information that it holds will grow commensurately. Retaining increasing varieties of customer data in a database with a rigid and inflexible schema leads to bottlenecks, inefficiencies and security issues.
So imagine that your company has decided to enable its customers to pay through a regular monthly debit program. In addition to credit card details, you may now need to store a more complex set of personal details. A customer table that formerly contained only names, addresses and a single nominated credit card is no longer sufficient. Splitting out the card number into a separate table, where you can then add in further banking details, can be effected with great efficiency through using the Table Split refactoring. After you have chosen a name for the new table the dialog lists all the columns in the original table and asks you to select which columns you want to copy or move to the new table.
At least one shared column must be included in both tables:
After selecting which columns you want in the secondary table you can specify on which table to create a foreign key.
SQL Refactor will then generate the creation SQL necessary for carrying out the table split. This script can be viewed and edited before execution.
With ever increasing complexity in database installations; with SOX and other compliance issues requiring increased transaction transparency; and with the database industry workforce increasing year on year, SQL Refactor is a timely tool that can make extremely light work of hitherto arduous and time-consuming tasks.
Bringing order out of chaos is the first step toward huge efficiency gains in database installation and maintenance.
