Updated 20-Jul-2007: Pricing information changed in last section; SELECT * expansion.
Today I spent some time playing with today's release of Red Gate's latest version of SQL Prompt, which I received through the Friends of Red Gate programme. The currently available version at the time of writing is 3.1. According to Bart, the final should be out by end of July.
SQL Prompt is a code completion add-on for Management Studio, Query Analyzer, and now also for Visual Studio 2005 within database projects.
Personally, I prefer to write SQL manually (not using the query builder) as I find it quicker, and so the offer of intellisense is very cool. That said, I was keen to check out whether this product (1) does what it says it does, and (2) would actually save time and improve the experience of working with SQL.
Latest features added in 3.5 include:
- Visual Studio 2005 database project support
- Cross database queries
- Distributed queries across linked SQL Server instances
- Correlated subqueries
- 3 – 5 times faster metadata retrieval
- Large script support
- 40 – 95% decrease in memory footprint
[I have never used this product before so some of the features I mention may already exist in version 3.1 or earlier.]
The installation was painless which is a good first sign. To be honest I've found that side of all Red Gate products to be very good, in that they are usually quite stable and robust.
After installation, I restarted SQL Server Management Studio, opened up a new query window. SQL Prompt began to build its cache. The cache evidently contains a representation of every object in the selected database so that it can perform the intellisense-like functions.
I noticed that creating a new table doesn't trigger a cache refresh, even when you enable the option to refresh the cache upon opening a new query window. However, It's easy to manually refresh the cache so this isn't too much of an issue.
The coolest things I found after a few minutes of experimenting were:
- SQL is automatically formatted, thus making your manually-written scripts nice and consistent.
- I liked how table aliases were added (this is configurable) and how subsequent column names were prefixed with the alias.
- Say you want column 'adminid' but this shows up in a few tables (FK's etc). You just start typing 'adm..' and then select the column from the desired table.
- Typing 'INSERT INTO <tablename>' expands into:
INSERT into [<Tablename] (
As you can see, the type is conveniently placed there in a comment, and a default value is there to save you time if you need it. Of course, some won't like this and the option to turn off the hints and defaults is available.
) VALUES (
/* int */ 0,
/* varchar(10) */ '',
/* int */ 0 )
One thing I would like to see when generating insert statements, is the option to not add columns which are marked as Identity. Currently, SQL Prompt adds every column, regardless of its Identity setting.
- When typing 'UPDATE <tablename> SET' SQL Prompt knows that you are in this context and shows you that table's column names in the menu.
Snippets are basically pre-defined sql templates.
For example, typing ALTER first brings up the list of object types that you can alter, which is standard behaviour.
You can then click on Snippets, and choose from a list of snippets which are related to the command ALTER. These include alter column, alter table, etc.
This is particularly useful if you don't live and breathe SQL and you are forever in BOL looking up T-SQL syntax! That in itself will surely save you many minutes per hour or database development.
Snippets are also pre-wired to aliases, e.g. at = ALTER TABLE
Of course you can define your own, which allows you to easily repeat the SQL statements you type often.
I did notice that some of the snippets are a bit light on implementation.
Some things that didn't work were:
- Typing 'SELECT * FROM <tablename>' then hitting TAB after the * is supposed to expand the * into the column names for that table, but it doesn't always seem to. That said, when this feature worked, it worked well with join queries too.
- If you type in: 'select * from <tablename> T where T.<col1> and then type another dot, the intellisense menu pops up again with all of T's columns. I think that the context detection should be better in this situation.
3.5 beta Features
I tested cross-database queries and these worked fine. The speed of metadata retrieval is definitely adequate and on my machine is faster than Resharper.
Correlated subqueries (where a statement in the subquery references a column in the parent query), such as:
SELECT R.[Name], a.[AdminName]
FROM [dbo].[Role] R, [dbo].[Administrator] a
WHERE roleid IN (
FROM [dbo].[AdministratorsInRoles] AIR
WHERE air.[AdminName] = a.[AdminName])
work fine - SQL Prompt seems to understand this contstruct without any trouble.
The product is clearly aimed at database-focussed developers who do a lot of work with SQL. Assuming the single-user licence price of US$195 remains the same at time of release, this offering is definitely worth the money in my opinion. The main reason I am saying this is purely due to the speed increase it will provide you while working with SQL. Red Gate also let me know that version 3.5 is a free upgrade for all current owners of 3.x.
Regarding price, lone developers/small teams may see this as expensive for a code completion tool but larger companies will not. Anyone who's come from stock-standard VS2005 to using Resharper or a similar add-on will understand the productivity gains at stake (note: Resharper is US$199).
Code completion is all about streamlining your working processes, and reducing the amount of tedium you have to endure on a daily basis! The product does as promised, which is essentially that it allows you to type in SQL much more rapidly, without having to switch windows to look up object names and definitions. SQL Prompt certainly makes time spent in the database that bit more fun and therein lies the perceived value.
To try version 3.1 for yourself, head over and download a 2 week free trial.
07-19-2007 11:03 AM