Skip to main content

Refactoring T-SQL

While much has been said about the benefits of refactoring application code (see VFPX Code Analyst), I typically haven't seen a lot of noise about refactoring T-SQL and stored procedure code.

The refactoring features found in SQL Manager tend to be more about refactoring your database design but not about the actual content of the SPs.

It's important to note that if you tend to rely on large stored procedures, then many of the same rules of refactoring apply:

1. Keep it short and sweet.
2. Make it readable.

So when dealing with a particularly unruly stored proc (over 1000 lines), I was quite happy to find Red Gate's SQL Refactor (here's a post from the lead developer).

Some of the features are pretty basic (renaming variables, etc) but the one of great interest was the Encapsulate as a new Stored Proc.

As with a number of tools that are add-ons to other components, its overall usefulness might seem limited if you are building your stored procs either using testing patterns or have good code review sessions - but if you're inheriting or just watching one procedure get too big, it's definitely useful.

What about your SQL applications? How big do you let your stored procedures get?
What tools do you use?


Popular posts from this blog

User Interface Design in Web and Windows

This is the white paper on user interface design that was given at a number of conferences including DevTeach 2003 and others.

From the overview:
"  Interfaces are a lot like people – no two are ever really alike. That can present a problem for developers and end-users. On one hand, you want to provide a clear , consistent and easy to use experience for the user. On the other, you want to showcase your graphical design skills. This session goes through some of the issues confronting interface designers today and how we can best deal with them. "

This session discusses the benefits of the Inductive User Interface, a concept that Microsoft initially showed in MS Money 2001 but has found its way into certain aspects of Windows XP, Office and other tools.

Is the Inductive Approach really the best way? Well, it certainly works for new users, who can't discern between icons. As I look at my tool bar in IE right now, I can easily see that the magnifying glass means "Se…