I’ve been using SQL Prompt for years. In September 2013, version 6 was released (quickly followed by 6.1 in late September and 6.2 in December ), so I thought I would highlight some of the new features available in it.
So lets start off with a table to show the new features and what version they were implemented in.
| Version | Feature | Description |
|---|---|---|
| 6.0 | Tab History | A history of query tabs opened in SSMS |
| 6.0 | Synonym support | Synonyms (including columns from a synonym’s base object) are now suggested |
| 6.0 | CLR support | CLR functions and stored procedures are now suggested |
| 6.0 | XML functions support | XML functions are now suggested and fully supported |
| 6.0 | SQL Azure support | SQL Azure databases are now supported |
| 6.0 | Custom snippets folder | Share snippets with other SQL Prompt users by using a shared snippets folder |
| 6.0 | More snippet placeholders | You can now use $PASTE$, $DBNAME$ and $SERVER$ in SQL Prompt snippets |
| 6.0 | Snippet placeholder format | You can specify custom $DATE$ and $TIME$ format |
| 6.0 | Indent using tab characters | You can customize SQL Prompt formatting to indent text using tab characters |
| 6.0 | JOIN clause improvements | All relevant tables are now suggested in a join clause |
| 6.0 | Copy from object definition box | You can now press CTRL + C to copy the contents of the object definition box |
| 6.1 | Server/database connection filtering | You can now specify the databases you want SQL Prompt to load suggestions for |
| 6.1 | Major performance improvements | |
| 6.1 | Added copy button back to object definition box | |
| 6.1 | Added option to disable synonym support | |
| 6.1 | Autocompletes ALTER statements with SQL Azure | |
| 6.2 | SQL Server 2014 support | SSMS and 2014 CTP2 Server |
| 6.2 | Phrase completion | SQL Prompt now completes entire SQL phrases, for example PRIMARY KEY instead of just the first keyword PRIMARY |
| 6.2 | Insert suggestions by pressing ";" key | |
| 6.2 | Tabs opened by SQL Prompt are no longer marked as unsaved | |
| 6.2 | Options dialog box is now resizable | |
| 6.2 | Improved formatting for MERGE statements |
And, of course, bug fixes.
So let’s look at what are some of my favorite new features. First up – new features in v6.0.
The added support for synonyms, CLR functions and XML functions seems to me to be a natural extension of the product. If you use these, and you use SQL Prompt, you need SQL Prompt to prompt for what they can with these.
The custom snippets folder is all about being able to share the snippets. Point all developers to a network share, and they can all use the same set of snippets. Snippet changes made will be available to everyone at once (though they might have to refresh suggestions first). If you have different teams with different needs, (dev team 1, dev team 2, dbas, etc.) they can each point to a different share.
In thinking about needs for multiple teams, I think that they would have some common needs across these teams. Maybe someday we’ll have the ability to specify a hierarchy of shared folders, so that you can have common snippets to everyone, and then snippets common to the teams. Of course, all the dev teams could need something different from the DBAs, so there is the hierarchy. Is this worthwhile? I don’t know… I just now came up with this idea and haven’t really thought about it much. If this gets implemented, the biggest issue I foresee is duplicate-named snippets – there might want to be a mechanism to select which ones are to be the final one.
The changes for snippet placeholders are just neat. $PASTE$ will insert the contents of the clipboard. $SERVER$ and $DBNAME$ will be replaced with the name of the connected SQL Server instance and database name. I can see some potential uses for these. But what I really like are the formatting options available for $DATE$ and $TIME$. These are expressed within parenthesis just before the ending $, and follow the .NET formatting conventions for dates and times. I use these in a snippet for creating what I term a remark block – a remarked out section at the top of code for tracking changes to the code. The snippet I use is:
/******************************************************************************
$CURSOR$Description of code
*******************************************************************************
MODIFICATION LOG
*******************************************************************************
$DATE(yyyy-MM-dd)$ WGS Initial creation.
******************************************************************************/
The resulting code will look like:
/******************************************************************************
Description of code
*******************************************************************************
MODIFICATION LOG
*******************************************************************************
2013-12-28 WGS Initial creation.
******************************************************************************/
(Can you guess on what day I created this?)
Next up is new features in the point releases. The big one in v6.1 is the ability to control the databases that SQL Prompt will load suggestions for. If you are in a small database, and in your query you use three-part naming to specify the database an object is in, SQL Prompt will load that database. While it’s loading, you have to wait. If it happens to be that database (you know, the one where every customer has their own table and set of procedures, and you have 250,000 customers), this can take a while. And take a serious hit on the memory on your workstation. So you can exclude this database from getting suggestions for, and live happily ever after. (Or until Red-Gate figures out how to use a memory-optimized table to hold all of this information.)
The big new feature in v6.2 if phrase completion. In previous versions of SQL Prompt, you would be prompted for keywords one at a time. (Sounds like a cursor… row-by-agonizing-row.) But there are places where when you use the first word, you will always use the second word. I’m thinking of “FOREIGN KEY” right now – the only place you ever use “FOREIGN”, you will also use “KEY”. Selecting them together is just another speed enhancement in writing your code (which is what SQL Prompt is all about in the first place!). The example that Red-Gate gives using “PRIMARY KEY” isn’t so clear cut – there are places where you will use “PRIMARY” without “KEY”, like when specifying filegroups. However, in the correct context, the phrase is also valid. Since it has already figured out the valid keywords for the current context, then phrase completion is, IMO, what should have been there in the first place. (I have to admit that I requested this feature – only to find out that they were already working on it!)
A quick table of the SQL Prompt release notes:
