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?
Ive 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 its 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, thats 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 Microsofts genuine IntelliSense, and its free. Does this make Red Gates SQL Prompt obsolete? Lets 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, heres the functionality it exposes, with a brief explanation of each:
- List Members
Press Ctl+J to pop open a list of members such as Tables, Fields, etc.
- Parameter Info
A pop-up control tip text that describes the parameter signature of the proc.
- Quick Info
A pop-up control tip text that gives information like column list or function signature.
- Complete Word
Once you type a few letters of an object, pressing ALT+RIGHT ARROW completes the word.
- 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.
- 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, Id 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.
- Column Picker
The column picker has been around since the beginning and though I havent used it much, its a great feature for those who are used to seeing this sort of interface in the SQL Server Query Window. Its 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).
- 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 Im able to type p_Com in the query window and SQL Prompt opens a list with the procs 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 doesnt 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 its something you do 20-30 times a day, youll notice the difference. (See screen shot below).
- Create and Load Snippets
This is the feature I cant live without and it doesnt 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 its now available.
- Format SQL Code (pro edition
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 dont like to have to reach for the SHIFT key. With this feature, I dont have to, and its 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.
The options settings in SQL Server 2008 are virtually nonexistent compared to what is available in SQL Prompt. Youre 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:
Is SQL Prompt Worth The Money?
I suppose the answer depends on a number of things:
- 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.
- 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.
- 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.
Youll notice that the cost in dollars and cents isnt included in my analysis. Its 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?