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?

Comments

Popular posts from this blog

Prototyping Applications with Sketchflow

How many times have you needed to present visual concepts behind your application to a client?

Over the years, I've had several times where I had to build a quick mock-up in Visual FoxPro to show how an application or function might run. Designing the screens themselves weren't the challenge - the challenge was showing the user how the application would flow between different areas. Short of putting fake code into buttons or saying "and now we go here", there really hasn't been an easy way to do this.

Now, sometimes, a client has gone so far as to hire someone to build "wireframes" to depict this. I found this process an entire waste of time. People building wireframes tend to be "business analysts", in short; people who have stopped learning how to develop code but still want to show their value by being the translator between the client and the developer. As far as I'm concerned, if you want to be a senior developer, you need to be able to…