SoftTreeTech SQL Assistant

By : Dinesh Asanka Sep 04, 2007

Printer friendly

Executive Summary

Product: SQL Assistant

Publisher: Softtreetech

Price: 1 developer license with 1 year maintenance and support for $149.00

Introduction

If you are a database developer, I am sure you are writing hundreds or thousands of T-SQL codes per day. When you are writing your T-SQL script, I'm sure you are spending more time to find the correct fields names, table names and their joining columns. If you have developed applications in visual studio, you will know that pressing ctrl + space or dot (.) , will give all the available commands in a list, so that you can select appropriate command from the list as in following image.

This is called intellisense. What about the having same feature for SQL Server Management Studio?  Apart from this, another considerable time drain is formatting your code - inserting tabs, line breaks etc. SQL Assistant, a new tool from Softtreetech, is released to support your all coding standings.

Installation and Configuration

Installation is as easy as other standard tools. However, there are few things you need to do in configuring the SQL Assistant. You can run SQL Assistant with minimum requirements of hard disk space of 3.2 MB, Memory - 128 MB, CPU - Pentium or compatible. It supports operating systems of windows 2000, XP, 2003 and Vista . However, I had some difficulties initially with installing in Windows Vista, which was solved later. This tool supports Microsoft SQL Server 2000 and 2005 databases.

You first select the editors that you need enable in SQL Assistant. As you can see, this tool is not limited for SQL Server products and it can cater for wide range of databases editors such as Oracle and mySQL. However this review is focused only on SQL Server Management Studio.   

You can remove the unwanted editors as well as including other editors. There are other configurations which we will discuss later.

Features Tested

Let us assume that you want to write a query to retrieve an employee name and the department(s) they are attached to. For this select query, you need to know the database name, table names, columns names and columns which employee and department table joins.

If you type SELECT * FROM at SQL Server Management Studio editor, you will see the following.

You can see that you will have the option of selecting the table name. You can see the database name and schema name as well. Apart from these, you can see the list of fields, their data types, length and whether the field is not null or null. So before selecting the table you know all the details about the table.

After selecting the employee table you then need to join the department table. We don't know the name of the department table. So just after giving the alias for the employee table(EM) and typing INNER JOIN you will get the following screen.

You can see those tables which are joining, employee table will be listed and you have the option of selecting correct table.

After that you need to get the joining columns for those two tables. After giving the alias you will be shown the following screen:

After selecting the correct join, you will need to select the department and contact tables to satisfy the above purpose. After following the same steps as above you will be end up with following query.

SELECT * FROM AdventureWorks.HumanResources.Employee EM INNER JOIN AdventureWorks.HumanResources.EmployeeDepartmentHistory EDH ON EDH.EmployeeID = EM.EmployeeID INNER JOIN AdventureWorks.HumanResources.Department DEP ON DEP.DepartmentID = EDH.DepartmentID INNER JOIN AdventureWorks.Person.Contact CON ON CON.ContactID = EM.ContactID

The next task is to select the correct columns. Just after typing the table alias, you will be shown a list of fields associated with that table as in the following screenshot:

After selecting all field names, this is the final query that ends up with.

SELECT EM.EmployeeID,con.FirstName,con.LastName,dep.Name FROM AdventureWorks.HumanResources.Employee EM INNER JOIN AdventureWorks.HumanResources.EmployeeDepartmentHistory EDH ON EDH.EmployeeID = EM.EmployeeID INNER JOIN AdventureWorks.HumanResources.Department DEP ON DEP.DepartmentID = EDH.DepartmentID INNER JOIN AdventureWorks.Person.Contact CON ON CON.ContactID = EM.ContactID

You can see that by spending minimum time and effort you have generated the required query code.

SQL Assistant provides support for many different SQL statements and options, including but not limited to INSERT, DELETE, UPDATE, ALTER, TRUNCATE, EXEC, SET and other. It automatically parses the code as you type it and comes up with the most appropriate suggestions. It provides handy help with procedure parameters, standard functions, system variables and number of other things. For Transact-SQL procedure developers it offers several additional features that simplify the coding and increase productivity, the most important is automatic suggestion of script variables after the @ symbol. This feature alone significantly decreases time needed to code complex stored procedures.

Next is to format your query, which is the most annoying thing for many database developers including myself.

If you right click the query after selecting it, you will get the followings et of options:

Out of those options, if you select the Format Code option or press Ctrl + F11, you will get a nicely formatted query like below:

SELECT EM.EmployeeID

,con.FirstName

,con.LastName

,dep.Name

FROM AdventureWorks.HumanResources.Employee EM INNER

JOIN AdventureWorks.HumanResources.EmployeeDepartmentHistory EDH

ON EDH.EmployeeID = EM.EmployeeID INNER

JOIN AdventureWorks.HumanResources.Department DEP

ON DEP.DepartmentID = EDH.DepartmentID INNER

JOIN AdventureWorks.Person.Contact CON

ON CON.ContactID = EM.ContactID