Editing T-SQL: The Road to Perfection

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.

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.