SQL Server Worldwide User's Group - January 3, 2008
Review: SoftTree SQL Assistant
by Stephen Wynkoop

Overview
SQL Assistant is a tool that integrates into your environment of choice, offering to help with your SQL coding.  The hope is that you'll end up with standardized code, better, more efficient coding time since the tool would help you create the code and more.  

I'm skeptical, frankly, when it comes to developer tools.  I think that the built-in tools for SQL Server management (Visual Studio, Management Studio, etc.) are pretty straightforward and simple to use and understand.  To make me want to install an application to work in addition to these -- well, it would have to be pretty special.  

Intellisense is just one aspect of this tool that is mentioned, and I fear it's the one that will get the most attention.  The reason I say this is because the tool includes so much more than "just" intellisense for your SQL tool of choice. In reviewing the tool, I installed it and simply let it do its thing as I worked with the databases and systems here at SSWUG.  

I was really surprised at how much SQL Assistant is able to help out as you're editing and working in your TSQL tools of choice.  I even opened up some scripts in Notepad and was able to get formatting and such - it's a nice touch.

Installation/Activation
Installation is straightforward and simple.  You can download the software from the site, be up and running after just answering the default prompts.  Once installed, you'll want to call up the Options dialog.  Double-click on the system tray icon and it will come up, giving you the ability to indicate the types of databases ("targets") that you'll work against.  

The defaults were odd, I thought, but I just went through and selected the tools (on the left) and then for the actual database engines, you set the selections on the right.  I would have liked it better if it either asked during installation, or went out to my system and tried to discover what I really had installed.  It's certainly not a big deal to set up, but would be a nice touch.

I don't know of any issues having environments selected that you're not actually using, but I tend to turn off things that I won't have any use for, perhaps out of habit.  So, I just disabled the editors I don't have and then double-check the options on the right.  Make sure the "SQL Assistance" and "Reference" options, are set up correctly, as should be the formatting style and snippets.

Do a quick check of the other tabs in the options dialog - it's not critical for initial usage, but I do suggest you go through and look at the settings.  This is how I learned quite a lot about the product and what it'll be doing.   Once completed, just click OK and the dialog will go away, back to watching and waiting for your time editing SQL-type code. 

The database selections include DB2, MySQL 5, Oracle, SQL Server, Sybase ASA, Sybase ASE - a great selection of environments. 

Note: make sure you're using version 3.x or later - the latest release from the site.

Usage
I think nearly everyone is familiar with the concept and implementation of "intellisense" type prompting.  Certainly if you're using Visual Studio or many other environments, you've had the experience if starting to type and then having the system "help out" by completing the statement as you type.  SQL Assistant is this for your work with the database, and it does a great job prompting you and providing options for what you're doing, then finishing your work for you.  



Pick and choose from the drop down, or just keep typing and it goes away.  Very nice in that the list box can be expanded or contracted, and as you type, SQL Assistant will be formatting your work, making sure it's consistent.  It can do all sorts of different things as you type in terms of formatting.  The huge benefit here is that your code, and that of others on your team, can be standardized, making it easier to follow in the future by others working with the code.  

Interestingly, the system comes with defaults as you'd expect for the different formatting options.  You can also control these though, making sure your own standards are applied for everything from SELECT statements to DELETE statements and stored procedures. 



For each of the different clauses, you can set up the model and manage the formatting.  Great flexibility and sensible defaults go a long way toward making this work right out of the box to help get you going in standardizing your formatting.  

I mentioned early in this review that I thought it would be a shame if the only thing SQL Assistant was really thought of for is the code formatting and intellisense type functionality.  I think this is where most people will start, sure.  But you'll be doing yourself a big disservice if you don't take a few minutes and really learn what SQL Assistant can do for you. 

One of the items is a statement builder - the ability to have SQL Assistant literally show you the options, you point and click at what you want to include and it builds the statement.  I know, it sounds odd, but let me show you a diagram first, then explain:

 

This assistant image is actually taken from within the SQL Server Management Studio editor - you can pick the statement or clause or function or..., well, just about anything from the list at the left, then the statement builder will show the construct to you.  Literally click on the sections you want to include, type the variable or constant value and the SQL Assistant is building the statement for you as you go. No more wondering about syntax or options, you'll be all set.  

You can call up a bit of code and reformat it to your standards.  You can jump around code to matching brackets, check spelling either after the fact or in real-time as you type.  you can drill down into objects on your system.  You can review procedure code, look at the SQL reference guide interactively (rather than opening a separate help window) within the editor.  

There are other goodies too, to help out with the browsing objects, code and other aspects of your system.  It gives you great "at your fingers" access to your system and the things you're working with as you work against your databases.

Conclusion
As I said, I was skeptical about the impact a tool like this can have on your productivity, on your team's work.  After using it for quite some time now, I can tell you I'd not hesitate to recommend that you download it, review the quick documentation, and use it.  At only $149 for a single license, I'd find it hard to imagine it wouldn't pay for itself very quickly with less time looking things up, less time wondering about syntax and better accuracy as you work with your system.  

You can download the demo, use it on your system and see how it works in with your daily life. I've found that I seriously miss the tool on my non-test systems.  I keep looking for it to pop-up and point out the schema, format the code, etc.  Once you have it, you'll find yourself wondering where it's been all this time. 

Ratings
Here are the ratings and brief commentary for the product.

Overall Rating: (5 stars)

Installation (5 stars)

Usage (5 stars)

Support  n/a 

Real-World Usefulness (5 stars)
This is one tool that nearly anyone working with databases could and should consider testing.

On the web:  http://www.softtreetech.com

Trial available?  Yes

About ratings:
5-outstanding, solid coverage of that product area
4-solid coverage of that aspect of the product
3-stars passing support, but nothing special
2-stars minimal functionality or support of that aspect of the product
1-star complete failure of that aspect of the product

Please note: While some products, services or other items reviewed on SSWUG may be those of sponsors, this in no-way impacts our rating or review process.