SQLServerCentral Article

How to Unlock Your Team's Creative Power with SQL Prompt and a VCS


If you spend any time programming T-SQL, you have probably heard of Red Gate's SQL Prompt. This is a leading productivity tool to help you write, format, and refactor SQL effortlessly.

One of SQL Prompt's most popular features is code snippets. You define a series of letters up front, called a 'snippet', and what code that represents, called a 'fragment'. Then, when typing in SQL Server Management Studio or Visual Studio, when you type your 'snippet' and hit TAB, SQL Prompt expands your code out.

The canonical example is 'ssf', which is a snippet for 'SELECT * FROM '. This saves you from typing 10 out of 14 characters each time:

SQL Prompt comes with a library of snippets already set up for you. As a new user you can add to, edit, or delete from this library via the Snippet Manager.

This is great, but isn't at all collaborative.

If you work on a team, it's likely you'll have shared standards, common helper utilities, and agreed boilerplate when writing more complex functions and stored procedures. SQL Prompt is great for individuals if you invest in creating time-saving snippets. But no one else on your team benefits unless you make them include the snippet as well.

Fortunately this is a solved problem.

SQL Prompt ultimately saves snippets as simple text files. As of SQL Prompt v6.4.1, its .sqlpromptsnippet files can be read at a line-level by version control systems, like Git or SVN.

This means that you can put snippets into a version controlled code repository, just like any other library or code base. And then, it can be shared with your team, and with the wider SQL developer community.

Let's start with setting up your snippets folder as a repository within your team:

  1. Make sure you are on SQL Prompt v6.4.1 or higher
  2. Find your snippet directory, which should be "%LOCALAPPDATA%\Red Gate\SQL Prompt 6\Snippets"
  3. Make this folder a repository using your preferred version control system, and encourage your team to do the same*
  4. Edit and add snippets using SQL Prompt's Snippet Manager interface as normal
  5. Branch, commit, push and pull snippets using your version control system

(*Note: A full review of Git and working with distributed version control is out of scope for this article. I recommend this tutorial for a short, fun, hands-on introduction to Git, and this page for a more comprehensive list of learning resources.)

Now, you can use version control to collaborate on snippets. Sharing your innovations is one 'push' away. Getting the latest and greatest snippets is as easy as a 'pull' from the shared repository.

The implications of this are quite powerful. At a very basic level, you can share best practice amongst the team faster than ever before. If someone designs a great snippet, it's incredibly easy for that to be shared with the rest of the team.

At a higher level, you can now use SQL Prompt snippets to store common team boilerplate. Most teams I have worked on have had a preferred way of laying out stored procedures. For example: where and where not to place comments, settings to enable and disable, where to define variables, how to handle errors, and so on. Now you can wrap that template up into a snippet, and it's incredibly easy for everyone on the team to adopt and work from the same starting point. No more copying code from one place to the next. And, if someone new joins the team, it's that much easier for them to adopt the team styles and patterns.
That's all for now. In the second part of this article I'll be extending this functionality, by linking to an open source library of snippets, and showcasing some extremely useful snippets from this library.
I'd love to know what you think about this improvement - please leave a comment on this article.
PS - A special thanks to Aaron Law at Red Gate, who recently shipped a feature making SQL Prompt snippets compatible with Git. This wouldn't be possible without that!


5 (3)




5 (3)