Red Gate SQL Prompt Versus Microsoft Intellisense

In this
economy IT professionals have to make smart choices when it comes to spending
money on developer tools. Does the new Intellisense feature of SQL Server
2008 Management Studio make third party intelligent code completion tools like Red Gate SQL
Prompt obsolete?

I’ve been a big fan of Red Gate tools for SQL Server since their first product
came out in 1999. About three years ago, they released SQL Prompt, an add-on
for SQL Server Management Studio that effectively integrated IntelliSense-like
functionality into the Query Window. You can still read my product
review
that was published on Database Journal back in 2007.

If ever there
was a SQL Server development tool that I would not be without, this is it.
When the DBAs and DBDs on my team got a look at it, they petitioned management
to purchase copies for everyone. While it’s not cheap, it is a good deal.
Single user license runs $195 and a 5-user license works out to $119 per
person. As a consultant, that’s only a few hours of billable time and my copy
has paid for itself many, many times over in productivity gain.

I mention the
cost off the bat because SQL Server professionals who want to leverage this
sort of IntelliSense now have a choice to make. Management Studio
that ships with SQL Server 2008 now includes Microsoft’s genuine IntelliSense,
and it’s free. Does this make Red Gate’s SQL Prompt obsolete? Let’s see.

What Can SQL Server IntelliSense Do?

The help file
for SQL Server 2008 Management Studio is online and one can read the full
explanation of the IntelliSense features at MSDN: Using
IntelliSense.

In a
nutshell, here’s the functionality it exposes, with a brief explanation of each:

  1. List Members
    Press
    Ctl+J to pop open a list of “members” such as Tables, Fields, etc.
  2. Parameter Info
    A
    pop-up control tip text that describes the parameter signature of the
    proc.
  3. Quick Info
    A
    pop-up control tip text that gives information like column list or
    function signature.
  4. Complete Word
    Once
    you type a few letters of an object, pressing ALT+RIGHT ARROW completes
    the word.
  5. Automatic Matching of Syntax Pairs
    Gives
    feedback when a closing delimiter (parenthesis or END statement) is
    missing.

Below are some sample
screen shots of how IntelliSense looks in Microsoft
SQL Server 2008 Management Studio. They demonstrate the List Members, Quick
Info and syntax matching.

These functions are
not always available. For example, if there is a code error above the cursor,
then these features fail to function. A full list of the exceptions may be
found in this MSDN article, MSDN: When
IntelliSense Is Unavailable.

What Can Red Gate SQL Prompt Do?

SQL Prompt
performs all of the functions mentioned above in the Microsoft IntelliSense
section, and more.

  1. Wildcard Expansion of Columns

    This is similar to the Quick
    Info feature described above but so much better. Rather than popping up a
    control tip box with a list of existing columns, SQL Prompt will expand the
    “star” to an actual list of columns embedded in your SELECT statement. So if I
    wanted 15 columns out of 20 available in the table, I’d just type SELECT * FROM
    MyTable, put the cursor at the * and press the TAB button. This action
    replaces the "*" with the column list, one column name per line.

  2. Column Picker

    The column picker has been
    around since the beginning and though I haven’t used it much, it’s a great
    feature for those who are used to seeing this sort of interface in the SQL
    Server Query Window. It’s basically an alternative to the process described above.
    Rather than striking TAB to auto-load a list of ALL columns, you simply check
    off the columns you wish to put in place of the “star”. (See screen shot
    below).

  3. View Schema Information

    When you type in a few
    letters, a List Members box opens up, as with the SQL Server 2008 tool, but as
    you highlight an object name, another window flys out with the script for that
    object. So in one of my databases I’m able to type p_Com in the query window
    and SQL Prompt opens a list with the proc’s full name. Another window then
    flys out with the script for that proc. I press a COPY button and I can paste
    the script in the window and work on the proc. (See screen shot below).

    The alternative to this is
    to execute SP_HELPTEXT with the full name of the proc, highlight the results
    (if sent to grid because it often doesn’t work when sent to text), copy the
    text with a Ctl+C and then paste it into the query window. Not that many more
    steps, but if it’s something you do 20-30 times a day, you’ll notice the
    difference. (See screen shot below).

  4. Create and Load Snippets

    This is the feature I can’t
    live without and it doesn’t exist in SQL Server 2008 Management Studio. SQL
    Prompt has, from the beginning, supplied a list of “snippets” that may be
    loaded into the query window by simply typing the first few characters of the
    snippet name and pressing TAB. SQL Prompt comes preloaded with a number of
    useful scripts in the Snippet library.

    You can also add your own snippets and they will become available as well.
    With version 4.0 the management of snippets has changed and they are now saved
    as files, which may be backed up and shared with others. If another user has a
    snippet to share, they simply send it to you and you place the snippet file
    into the default folder for snippets. Voillia … it’s now available.

  5. Format SQL Code (pro edition
    only)

    Another feature I love is
    the automatic formatting of my SQL code. Call me anal if you must, but I like
    it when all keywords are capitalized but I don’t like to have to reach for the
    SHIFT key. With this feature, I don’t have to, and it’s another little thing I
    miss when using SQL Server 2008 tools. You can also define how you want your
    SQL statements formatted, where line breaks occur and things like whether to
    include spaces after commas and on either side of an equals sign. There are
    many other options settings for line formatting, enough to meet just about
    anyone’s idea of how their T-SQL should look.

  6. Option
    Settings

    The
    options settings in SQL Server 2008 are virtually nonexistent compared to what
    is available in SQL Prompt. You’re given the ability to do things like control
    the list behavior, hide system objects, default column matching on joined
    tables and many, many more options.

Below are
some screen shots of benefits of SQL Prompt described above. There are really
so very many options and features, an exhaustive article would be difficult to
write and probably boring to read. The full description of what the product
does is, of course, at the Red Gate web
site
:

screen shots of benefits of SQL Prompt

screen shots of benefits of SQL Prompt

Is SQL Prompt Worth The
Money?

I suppose the
answer depends on a number of things:

  1. Do you even
    have another option? That is, are SQL Server 2008 tools even available to
    you? SQL Prompt works with SQL Server 2005 Management Studio as well.
  2. How much do
    you work with T-SQL on a daily basis? If you write a lot of T-SQL then
    you stand to save a lot of time. Also, it reduces the odds you will
    misspell a table or column, which costs one more time debugging, editing
    and rerunning.
  3. Do you
    value IntelliSense style code assistance? I started coding with VB and
    VBA in Microsoft Access and have become accustomed to it. If given the
    option, I’d personally always opt for it but I suppose some find the
    pop-ups annoying.

You’ll notice
that the cost in dollars and cents isn’t included in my analysis. It’s a moot
point, in my opinion, because time is money and Red Gate SQL Prompt will save
you time, more time than the tools now bundled with SQL Server 2008. Sure,
Microsoft may catch up with the toolset in the next generation of SQL Server,
but can you really afford to wait? Besides, by that time, who knows what the geniuses
at Red Gate will have come up with for version 5 of SQL Prompt?

»


See All Articles by Columnist

Danny Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles