Editing T-SQL: The Road to Perfection

A comparative review of T-SQL editors

Call me crazy, but I’ve always preferred using the native tools that ship with SQL Server. With SQL Server 2000, Enterprise Manager always served my needs from an administrator’s perspective (even though I still shriek if I see someone trying to edit code with it), and I’ve always been extremely happy both as an admin and a developer with Query Analyzer as a T-SQL editor. Likewise, SQL Server Management Studio (SSMS) serves me quite well with SQL Server 2005, even though as an avid .NET developer I’m crushed that it wasn’t able to deliver on the Intellisense and collapsible code regions promised back in the early days (when SSMS was called “SQL Workbench”).

I’ve tried using third-party editors and management solutions in the past, but they’ve never really held my attention. If it weren’t for the fact that I was so eager to make the transition to SQL Server 2005, I’d have to wonder if I’m not too set in my ways because I show very little inclination toward learning how to use third-party tools. I think my major issue is that I like SQL Server so much that I’m particularly reluctant to let any non-native tool take focus away from my constant thirst to learn more about it. My hunch, however, is that I’m in good company, and I’m confident that many other T-SQL developers (and pro-scripting DBAs) prefer using native tools themselves.

In this article, therefore, I’ve looked at each of the T-SQL editors under review with a very critical eye. Prior to evaluating each of these solutions, I decided that although I’d likely be wowed by various features and points of functionality, the most important criterion I’d be using to judge each editor, after learning how it worked, was the degree to which I’d consider using it rather than the native SQL Server tools. You’ll find additional information about features for all three products in Table 1.

Embarcadero Technologies Rapid SQL 7.4

I’ve used previous versions of Rapid SQL, although I was usually too busy to give the tool much of a chance. During the course of this review I was glad to note a number of changes to the Rapid SQL interface, and I was also happy to realize that many of my perceptions about the tool needed to be seriously amended.

Embarcadero makes downloading and testing Rapid SQL 7.4 a painless process by providing a fully functional 14-day trial version of the product on its Web site. Installation went without a hitch, and I was easily able to connect to my servers and begin writing code. Upon connecting to one of my servers, I took immediate note of the ways in which Rapid SQL’s Object Explorer outshines the functionality provided by SQL Server and SSMS (as well as the other two products reviewed in this article). Unlike other Object Explorers, which are merely hierarchical in nature, Rapid SQL provides the same hierarchical approach but also groups objects by types that are more easily and readily accessible—a nice feature, as Figure 1, shows.

Although I found the UI intuitive, I wasn’t happy with having to click between tabs to toggle between my SQL statements and result-sets. The manual for Rapid SQL weighs in at a hefty 940-plus pages (9.5MB as a .pdf file), but I wasn’t able to easily find a way to “toggle” my results into the primary tab I was working in. Despite this small inconvenience, Rapid SQL performed admirably and was consistently quick and responsive.

Rapid SQL’s main strength is in providing a solid development IDE for heterogeneous database platforms. As a T-SQL editor, Rapid SQL provides decent syntax highlighting and even offers collapsible code regions and autoformatting functionality—both big wins. However, despite all of the strengths that Rapid SQL has to offer, especially in heterogeneous environments, I don’t think I’d use it in place of a dedicated T-SQL editor in a purely SQL Server environment. I can’t really explain why, mostly because (from this perspective only) Rapid SQL doesn’t really provide anything that would compel me to ditch all of the time and energy I’ve invested in mastering the native SQL Server tools that I already use.



PROS: Heterogeneous database support; code autoformatting, syntax highlighting, and Source Control integration
CONS: No support for graphical execution plans and no Intellisense; UI is a bit rugged; extremely unresponsive sales team
RATING: 4 out of 5
PRICE: Starts at $995 for the single-platform Pro version and $2895 for multiplatform Pro versions
RECOMMENDATION: Would be an asset in heterogeneous environments but isn’t a viable substitute for native SQL Server development tools.
CONTACT: Embarcadero Technologies * 415-834-3131 * http://www.embarcadero.com



DTM Database Tools DTM SQL Editor 2.02 Enterprise Edition

Prior to this review, I had never heard of DTM Database Tools DTM SQL editor, so taking it for a spin was a complete trip into the unknown for me. DTM SQL editor provided a fine user experience, although I did run into a couple of unresolvable problems with its export functionality. Despite that, however, the tool was very responsive and provided an intuitive interface, although its documentation is extremely light.

Installation of DTM SQL editor went flawlessly and completed far more quickly than the installers for the other products under review. Connecting to a database with DTM SQL editor was a bit more involved than connecting with the other SQL editors, but that’s because of the great support DTM SQL editor provides for heterogeneous data sources, including file-based databases. The Object Explorer provided by DTM SQL editor offered a different mix of detail than what I’m used to from Query Analyzer and SSMS, as Figure 2 shows, but provided decent coverage of objects, despite the lack of a dedicated node for user-defined functions (UDFs), which you access via the PROCEDURES node in this product.

DTM SQL editor’s main strength lies in its pluggable, or extensible, architecture. Out of the box, DTM SQL editor provides functionality that puts it in the same league with the other products compared in this article (albeit at the lower end). What sets DTM SQL editor apart is its affordable price tag and ability to “bolt on” additional functionality through the use of pluggable modules. Modules range in price from around $79 to $149 and include complex export functionality as well as the ability to generate test data, run load tests, or document existing schema.

In terms of code editing, DTM SQL editor doesn’t provide autoformatting, and syntax highlighting was rudimentary. The product does provide Intellisense, but I found it to be cumbersome at some points—the supplied list of objects was in a fixed-width window that didn’t let me see which table I was accessing when the schema name was particularly long.

Overall, DTM SQL editor has some definite strengths that revolve mostly around interacting with heterogeneous data sources. Accordingly, I’d be happy to use the product if I were in a highly diversified environment (and DTM SQL editor compares favorably with Rapid SQL, especially with respect to price), but in a purely SQL Server environment, I wouldn’t switch to it asa T-SQL editor.



PROS: Pluggable architecture: editor can be combined with other tools and options; heterogeneous database support
CONS: Schema extraction and export functionality wouldn’t work in testing; interface is a bit rugged; tool could use some additional functionality
RATING: 4 1/2 out of 5
PRICE: $275 for Enterprise Edition; plug-ins available at extra cost
RECOMMENDATION: Would be at home in enterprises needing to manage multiple platforms. The functionality and interface rank below that of the other two products but the price is drastically cheaper.
CONTACT: DTM Database Tools * http://www.sqledit.com



ApexSQL Tools ApexSQL Edit 2005.02

Unlike Rapid SQL and DTM SQL editor, ApexSQL Edit is a solution that has been specifically targeted to SQL Server. For me, this difference was immediately apparent. Installing and setting up ApexSQL Edit was a smooth process. Upon opening the editor, I was surprised at how polished and user-friendly the UI, which you can see in Figure 3, is. I also appreciated that many of the keyboard shortcuts that I have come to know and use with Query Analyzer and SSMS are included in ApexSQL Edit.

The Object Explorer that ApexSQL Edit provides exposes a wide variety of objects, matching very closely the richness found in Rapid SQL. I found interacting with ApexSQL Edit to be a real joy—everything opened quickly, and I never got the impression that I was waiting on anything while editing, querying, or interrogating objects.

Of all the SQL editors I’ve tried (including a few in addition to those in this review), ApexSQL Edit has the best Intellisense. In fact, I found myself thinking that ApexSQL Edit offers more of a “Visual Studio” feeling to editing T-SQL than SSMS does, which is terribly ironic. Overall, I was pleased by ApexSQL Edit’s code formatting, syntax highlighting, collapsible code regions, and Intellisense. I also enjoyed its particular approach to the use of code snippets, as well as its approach to using quick replacements—custom tokens that you can easily configure for immediate substitution as you type. (For example, if I type “!loj”—minus the quotation marks—my text would be immediately replaced with LEFT OUTER JOIN after I put a space after the final “j.”)

ApexSQL Edit also offers a plethora of output and result-set filtering options, which analysts will likely find attractive. ApexSQL Edit also offers native support for NUnit integration, making unit testing an easy possibility from directly within the tool itself. There’s even a handy “auto-rollback” mode you can enter by toggling a button in the IDE that will automatically roll back any SQL statement you execute—a feature I found a lot handier than I would have expected.

I really didn’t find any negatives in ApexSQL Edit: It’s a great tool, which is why I’ve designated it as my Editor’s Choice. I did notice that it has an annoying habit of switching database context back and forth between the current database and the tempdb when executing queries, but other than that, the product behaved flawlessly during my trials. I’d therefore highly recommend that any T-SQL developer take ApexSQL Edit for a spin. I’d consider recommending it as a suitable replacement for SSMS and even, possibly, for Query Analyzer.

Desert Island Picks

I had a good time evaluating the editors reviewed in this article. They’re all solid tools in their own right, but only one of them really has the focus necessary (in my mind) to compete with Query Analyzer and, to a lesser extent, SSMS. On the one hand, Embarcadero’s Rapid SQL and DTM Database Tools’ DTM SQL editor are both focused heavily on the same market and are suited to DBAs and ad hoc developers who spend a lot of time in heterogeneous environments. ApexSQL Edit, on the other hand, has been targeted toward only one platform, and that focus shows. I didn’t anticipate, prior to working on this review, that I’d ever find a T-SQL editor that I could like enough to compare with Query Analyzer or SSMS. I’m not 100 percent convinced that ApexSQL Edit is better than either of those tools, but I do feel confident in placing it in the same ballpark. I can see myself potentially complaining that there is no ubertool to combine the best features and functionality of SSMS, Query Analyzer, and ApexSQL Edit, but in terms of code editing, I’m sure I’d be fine if left on a desert island with any of the three. If you knew how much of a curmudgeon I am, you’d understand why that’s a huge compliment to the folks at ApexSQL.


PROS: Polished and intuitive interface; excellent snippet, Intellisense, and code-outlining functionality; provides excellent editing functionality and advanced source-control and testing functionality
CONS: No support for T-SQL templates
RATING: 5 out of 5
PRICE: Starts at $349; volume discounts are available
RECOMMENDATION: Powerful and well-executed enough to be considered a true replacement for Query Analyzer and SQL Server Management Studio.
CONTACT: Apex SQL Tools * 919-968-8444 * http://www.apexsql.com


Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.