SoftTree Technologies SQL Assistant 2.5

SoftTree Technologies SQL Assistant (SA) 2.5 promises to enhance the productivity and effectiveness of database developers working with Microsoft SQL Server 2005 and 2000; MySQL 5; IBM DB2; UDB 9, 8, and 7, or Oracle 10g, 9i, and 8i. SA runs in the background, and you access its productivity features via hotkeys or a right-click menu from within your chosen development environment. Soft-Tree might have an update to SA available shortly after you read this, offering support for advanced dynamically generated code snippets; temporary table references and table variables; multiple code formatting styles; and more preconfigured SQL editors and IDEs.

SA connects to the target database system to gain access to the object data - for example, table and view entries - that it needs. When possible, it will use an existing connection, sharing SQL Query Analyzer's ODBC connection or SQL Server Management Studio's SMO connection for access to catalog tables and views. For access to user-defined objects, SA will open - and remember - a second connection by using credentials that you provide. To get a quick start in some familiar environments, I installed SA to a system with a full installation of SQL Server 2000 and another with SQL Server 2005. SA installed quickly, placing itself in the Startup group and running with a system tray icon. SA is preconfigured for use with 12 editing programs, including Notepad, Query Analyzer, SQL Server Enterprise Manager, and SQL Server Management Studio.

SA's functions are accessible in two ways from within supported text editors: direct access to specific functions through the use of hot keys, and via integration with the editing program's right-click menus. To get a sense for SA's native capabilities, I chose to start with Notepad. I started on the SQL Server 2000 system by configuring Notepad for SQL Server 2000 - a simple process of selection from three dropdown boxes on the Targets tab in SA's Options menu. Opening a saved query in Notepad, I worked through SA's functions. A right-click in Notepad presented SQL Assistant as an option that displayed a submenu.Figure 1, page 46, shows an example from SQL Server Management Studio. Selecting SQL Reference or Code Structure opens a pane along the left side of the window with Reference and Structure tabs.

The Reference section lets you browse through SQL topics, showing a concise summary of statement structure and syntax, and allowing you to insert statement structures at the current cursor position in your code. With the cursor on a particular statement, Reference takes you directly to relevant information. When several statement formats are available - for example, Declare lets you declare a variable or a cursor - SA displays the alternatives. Clicking one of these alternatives inserts that statement structure at the cursor position.

The Structure tab displays the overall structure of the statements in your code. Plus (+) and minus (-) characters let you expand and contract the display. Clicking a structure element takes you to that point in your code for quick navigation.

In my tests, context-sensitive code completion was a real time saver. As I typed in a statement, potentially relevant object names from the server I was connected to popped up in list format. In my testing, this feature worked relatively quickly, exhibiting only a second or two delay as the code is being entered. I could use the mouse or keyboard to scroll through the list, then either left-click or press the Enter key to insert the selected item. For example, when completing a SELECT statement, keying the space following the SELECT keyword displayed a list that included system tables, views, functions, and databases. Keying the first letter of the desired object name narrowed the list. Using the arrow keys or typing on the keyboard took me to the desired object, and pressing Enter inserted the name. When entering a fully qualified field name, keying the period (.) character displayed the names of the next level of objects, allowing the automatic statement completion to continue. At the completion of a field, keying the comma (,) character displayed the original object list again to let me select another field name.

Another useful component is the syntax checker, which you access via the right-click menu or (by default) the Control + F9 hotkey combination. Error messages appear in a pane at the bottom of the window. I noticed that error detection is sometimes progressive: Correcting one error will reveal others. Other functions performed by SA include code formatting and rapid insertion of named code snippets. A code reference to a stored procedure turns into a hotlink when you place the cursor on the name and press the Control key. Clicking the link displays the underlying procedure definition.

SA can be a very useful tool, speeding up SQL coding and improving your accuracy. That said, I do see some room for improvement. For example, when building a Select statement the ability to select and insert multiple field names in a single operation would be helpful. I would also like to be able to create a complete skeleton statement in a single operation in a way that would allow the contextsensitive auto-completion tool to help complete the various clauses of a complete statement. Nevertheless, I believe that SA is well worth the price for those who spend their days coding and for those who have a regular need to construct SQL code - but perhaps not enough to maintain top skill levels.