Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
First impressions of SQL Prompt 3.5 beta

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] (
    [recId],
    [desc],
    [otherId]
    ) VALUES (
    /* int */ 0,
    /* varchar(10) */ '',
    /* int */ 0 )
    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.

    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
    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 (
SELECT air.[RoleId]
FROM [dbo].[AdministratorsInRoles] AIR
WHERE air.[AdminName] = a.[AdminName])

work fine - SQL Prompt seems to understand this contstruct without any trouble.
 

Wishlist

  • When typing where clauses, it would be nice to be shown the type of the current column and possibly insertion of quotes for varchar, text, date, etc.

  • When typing in a select statement such as this:
    SELECT [dbo].[AdministratorLogin].[AdministratorId]
    FROM [dbo].[AdministratorLogin] AL
    WHERE AL.[AdministratorId] = 1
    SQL Server reports the error "The multi-part identifier "dbo.AdministratorLogin.AdministratorId" could not be bound."

    This is a valid error of course, but I think it would be good if SQL Prompt could replace '[dbo].[AdministratorLogin]' with 'AL' automatically, at the time that it adds the alias.


Bottom Line

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.


Posted 07-19-2007 11:03 AM by Joe Niland

[Advertisement]

Comments

Bart Read wrote re: First impressions of SQL Prompt 3.5 beta
on 07-19-2007 7:00 AM

Wow that was quick off the mark! Thanks for the positive review Joe.

As far as the pricing goes 3.5 is a free upgrade for users who've already purchased 3.0/3.1 so you don't need to worry about spending another $200. For new purchasers we offer the usual Red Gate volume discounts.

With regard to your "SELECT *" column expansion problem could you send me an example of your SQL please so I can try to figure out what's happening? You can get hold of me at bartDOTreadATredHYPHENgateDOTcom. We've not seen any problems with this but that's not to say they don't exist and obviously we'd like to fix them before the final release.

Thanks again for the great review!

Bart

Joe Niland wrote re: First impressions of SQL Prompt 3.5 beta
on 07-19-2007 7:20 PM

Hi Bart,

Thanks for letting us know about the pricing - I've updated the post accordingly.

I've mailed you the details re: select * expansion.

Cheers!

Joe

Bart Read wrote re: First impressions of SQL Prompt 3.5 beta
on 07-20-2007 7:06 AM

Cheers Joe, much appreciated. I've asked our testers to take a look at the SELECT * expansion problem.

Bart

TrackBack wrote http://simple-talk.com/community/blogs/bart/archive/2007/07/17/33723.aspx
on 07-23-2007 1:30 AM
TrackBack wrote http://larkware.com/dg8/thedailygrind1189.aspx
on 07-23-2007 1:43 AM
zeppo d wrote re: First impressions of SQL Prompt 3.5 beta
on 11-12-2007 10:28 PM
i concur: a thoughtful and cogent analysis, although a little controversial in parts.

Add a Comment

(required)  
(optional)
(required)  
Remember Me?

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)