This blog has been moved to

Monday, December 11, 2006

The beta for SQL Prompt 3 is now available

I just installed the beta for SQL Prompt 3 today. For those that don't know what SQL Prompt is, it is basically Intellisense for Query Analyzer. They are currently giving away v2, but they are planning on selling v3 (get v2 while it lasts). If you like Intellisense in Visual Studio and you also write T-SQL, you'll probably appreciate the productivity improvements with SQL Prompt.

They've made some great improvements to the Intellisense dropdown. It is, of course, context sensitive, but it is also organized in categories. It allows you to view just a certain set of candidates (such as columns or functions). It's vaguely reminiscent of Visual Assist X (VA). It would be nice if they made it work more like VA. One of the things I like most about VA is that I can type any part of a method/property/etc and it will list it in the dropdown. If you have a couple of hundred choices, being able to type "order" and getting all of the candidates that include "order" somewhere in the name is very handy!

Another feature that I really like about the new SQL Prompt 3 is the auto-expand. If you type out a stored procedure name, SQL Prompt 3 can automatically expand the statement to include the parameters as well. You can also expand a * to the complete list of columns simply by hitting TAB when the caret is next to the *.

SQL Prompt 3 offers many configurable options to allow you to setup your environment the way you like it. It also includes the ability to add snippets that can be automatically expanded for common or complex SQL statements.

I'm glad they decided to limit the number of products they support. I use Query Analyzer exclusively for writing SQL (I tried using Visual Studio, but could never get used to it). I would much prefer a product that works very well for one or two products than one that almost works ok for a half dozen very different products. One thing I often wonder is why they didn't just create their own IDE for SQL scripts. It seems like it would integrate well with their other products (which I haven't used, but they look intriguing). It would be nice to have a SQL IDE that integrated with VSS. Perhaps they could have even integrated it with Visual Studio (or at least be able to open up Visual Studio projects to get to the SQL scripts embedded in them).

A word of warning, the Automatic Closing Characters feature doesn't work very well. In fact, I had to turn it off. Often times I would type a single tick and it wouldn't add the second tick. However, when I added the second one, it would add a third! Fairly annoying. Even when it did work correctly, it still suffers the same annoying problem as most tools that implement this which is if you type the closing character yourself, you end up having to remove the extras. VA handles this much better. It seems to remember when it automatically adds a closing char and will remove it if you type it yourself (VA is the first tool that I have not turned this feature off).

Another issue that I've seen with SQL Prompt 3 are that the columns in the dropdown cannot be resized. This means I usually only see the first part of the column name and rarely see the column type. I can expand the whole dropdown, but all of the columns expand with it. This means the dropdown has to be absurdly wide in order to show the entire column name (good thing I have a widescreen monitor :).

If red-gate is reading this, a couple of features that I would like to see in the release (at least the next version), are...

  • Expand UPDATE and INSERT statements. It would really save me a lot of time if I was able to type "UPDATE MyTable[TAB]" and have the columns laid out for me similar to the auto-expand feature for stored procedures. It's much easier to delete the ones I don't want (even if it's most of them) than to add the ones I do want.
  • Being able to view a list of references in a script. For instance, view all the lines that use the Employee table or the FirstName column from the Employee table, or the @DoSomething variable.
  • Provide some SQL refactoring capabilities. Changing the name of a variable is the only one that comes to mind, but I'm sure if I spent some time on this I would find some other useful refactorings in a SQL script.
  • Spell check. Who can't use a good spell check program to check their comments :). This is something I didn't realize I needed in an IDE until I installed VA.

If you develop T-SQL scripts, SQL Prompt 3 will greatly improve your productivity. This is a must-have tool for all T-SQL developers.

Here's the link to the announcement on red-gates website...

This post turned out much longer than I planned :). I hope you have found the information useful.


Anonymous said...

If you like SQL Prompt, try SQL Assistant from SoftTree Technologies! It's less expensive and more robust.

Anonymous said...

For my money, I like SqlAssist by Round Polygons ( I find it less intrusive than SQL Prompt, and works quite nicely.