Templates and teamwork

Revised for 2012

Since I wrote the original 2008 article a lot of changes have taken place both in terms of technology and in terms of the practices that were radical then but more mainstream now.

Technology wise we have seen significant releases for both Windows and SQL Server.  2008, 2008R2 and now 2012.

Back in 2008 I had barely heard of "agile" and I feel that it is fair to say that I wasn't alone in feeling sceptical about its applicability to the data world.  As of December 2012 I am convinced that following the disiplines of agile development can deliver the same benefits in the data world that have been enjoyed by the software development world.

Collaboration and sharing is a huge part of agile as is providing the development team (which includes DBAs) the environment and support they need and trust them to get the job done.

I believe the fact that tools such as Red-Gate SQL Prompt and Red-Gate Data Generator expose their configurations in the form of files on the operating system naturally lends them to allow collaborative implementation.

As such I feel that this article has gained relevance rather than aged into irrelevance.

Introduction

This article was intended as an adendum to an article written by Greg Larson back in 2003.  He wrote what was pretty much an all-encompassing article on using query analyser templates within SQL Query Analyser.

I suspect that virtually all of us have migrated to SQL Management Studio with very few SQL Query Analyser users still left but I still recommend reading Greg's work as it applies to all versions of the SQL Server tools.

SQL Query Analyser templates and SQL Management Studio templates

Another improvement in SQL2012 Management Studio is that it no-longer interprets XMLfragments as template place markers. Previous versions incorrectly interpreted the XML fragment as being a template place marker and corrupted the resulting code.

Team templates

If you work in a team then the chances are that anything worth putting in a template is worth sharing. There are a few things you need to think about if you are going to do this.

Storage Structure for templates

Using the View menu as shown below or CTRL + T bring up the template explorer.

As you will see the template folders, of which there are a great deal, are listed in alphabetical order.

At first alphabetical order seems logical but if you find yourself using the "Table" and "Stored Procedure" templates a lot it quickly becomes tiresome to keep having to scroll down the list.

I have already said that the SQL Management Studio templates are stored in the user profile so on my box they are stored under

%USERPROFILE%\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql\

To get the "0. Favourites" folder at the top of the template I have simply created a sub folder

%USERPROFILE%\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql\0. Favourites\

By numbering our folders we are ensuring that our folders go to the top of the list. As ordering is alphabetic you need to remember that 1, 10 and 100 will appear before 2, 20, 200 in the list so if you are going to have more than 10 folders you are going to have to pad them with prefixing zeros. I suspect that if you reach the stage where you need to prefix then you need to go back and rethink your structuring strategy.

The choice of favourites is a matter of personal choice so I recommend that all users set up a "0. Favourites" folder. This should not be overwritten from the master source and should remain specific to the user.

As to any other folders you may create for the team, if you are going to create for the team then you need to speak to the team to decide what is going to be useful and what precedence it should take.

Peer review and checking templates

I've got snippets of code that I use day to day. They are really a bag of useful tricks rather than something I would deploy to my colleagues. I know what the weaknesses are in my snippets and how they have to be adjusted to suit different scenarios but I shouldn't assume that my colleagues will think or work in the same way.

All this is a long winded way of saying that shared code needs to be clear, unambiguous, bug free and easy to understand. The best way of achieving this is to get at least one member of your team to peer review it. It is going to be used by the team so the team need to be comfortable with it.

Deployment method

Shared Template Repository

Back in 2008 I recommended setting up a shared repository for the shared templates and mapping a drive letter to that share.  My original example used the "T:" driver with a folder as shown below:-

T:\Tools\SQL Management Studio\

Although this method still works it does rely on everyone agreeing a single shared drive and given the complexity of modern environments this can cause problems.  Today I feel that a more appropriate location for holding templates and other shared artefacts is under source control using a tool such as Subversion.

Deployment Windows Command/Batch File

The next step is to set up a Windows command/batch file to copy all templates held within this folder down to your team's local drives. I call this DownloadManagementStudioTemplates.cmd

Back in 2008 I suggested using a single XCOPY command as follows

XCOPY "T:\Tools\SQL Management Studio" "%USERPROFILE%\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql" /i/e/c/y

The use of the %USERPROFILE% environment variable ensures that the templates will copy to the correct directory on any of your team's PCs.

The XCOPY switches used are as follows

In 2012 if you are using Subversion then the batch command will hold a command similar to the one shown below

svn update c:\documents and settings\david\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\

Running the Windows Command/Batch File

Again, back in 2008 I recommended have a 2nd command file whose purpose was to copy the command file described above to the  \Start Menu\Programs\Startup folder. This ensured that whenever a user logged on they gott the most up-to-date set of templates.

I called this original file InstallSQLTemplates.cmd which itself contained a single XCOPY command as follows

XCOPY "T:\Tools\DownloadManagementStudioTemplates.cmd" "%USERPROFILE%\Start Menu\Programs\Startup" /c/y

Again, the XCOPY command could be replaced with a svn update command however I would resist doing so for a couple of reasons

1. We are really talking about a one off copy of the DownloadManagementStudioTemplates.cmd to the Startup folder.
2. Putting a system folder under version control does not feel like a sensible thing to do.

Refreshing the templates

Whenever a team member logs on they will get the most up-to-date set of shared templates. When they go into SQL Management Studio those templates will be available to them.

You need to be aware that SQL Management Studio grabs a list of templates when it starts up. It does not have a refresh facility should the contents of your local template folder be changed while it is running. If you do update the template folder then you will have to shut down SQL Management Studio and restart it.

Using the Technique With Red-Gate SQL Prompt Snippets

The basics

Red-Gate SQL Prompt provides SQL intellisense and code refactoring for SQL Management Studio.

Useful though this is a much more interesting feature of SQL Prompt is its "Snippets" feature. A snippet being a piece of boiler plate text that you wish to attach to a macro. For example, typing

scf

allows the user to hit a key and have this expanded to

SELECT COUNT(*) FROM

The snippets supplied with the product are quite short but as the package provides the facility to add/amend these items there is no reason why larger blocks of text cannot be attached to key combinations. Indeed I have used such a technique in Eleven Challenges in Unifying Reference Data (See Challenge Four).

Following this train of thought and what SQL Prompt snippets can provide is a sub-templating facility.

At this point I should like to point out that sub-templating can be achieved wihtin SQL Management Studio itself using the following technique

1. Select a template in the template explorer
2. Press CTRL + C or right-click and choose COPY
3. In your query window click where you want to insert the contents of the template
4. Press CTRL + V or right-click and choose PASTE

SQL Prompt 5 stores its snippets in the following folder.

%USERPROFILE%\Local Settings\Application Data\Red Gate\SQL Prompt 5\Snippets\

A snippet file will be stored in the format <snippet shortcut text>.sqlpromptsnippet so the scf example (SELECT COUNT(*) FROM) used earlier will be held in scf.sqlpromptsnippet

Again, the snippets can be managed in a central repository whether this is a  network share of a source control system.

Additional Considerations for SQL Prompt

As for SQL Mangement Studio templates the list of templates is only updated when SQL Management Studio starts.  When I started using the techniques described here I did have a few frustrating moments until I realised that there was a need to restart SQL Management Studio.

Red-Gate Data Generator

I am still experimenting with Red-Get Data Generator but again, it stores its configurations in files that can be copied from a shared repository.  On your local machine these files will be held at the following location

%programfiles%\Red Gate\SQL Data Generator 2\Config\

An example of a bespoke Red-Gate Data Generator configuration

I decided to set up a data set for UK Counties.  A quick look at the USCity configuration showed that I would need two files

• UKCounties.txt - contains the actual list of counties, one per line
• UKCounties.xml - contains the configuration of how UKCounties.txt would behave.

UKCounties.xml has a structure as shown below:-

<?xml version="1.0" encoding="iso-8859-1"?>
<generators>
    <generator
        type="RedGate.SQLDataGenerator.ExpressionGenerators.FileListGenerator"
        name="UK Counties"
        description="Cheshire, Lancashire, Shropshire, Yorkshire..."
        category="Geographical">
        <property name="FileName">UKCounties.txt</property>
        <matches field="County" score="60" minlen="10"/>
        <matches field="Addr.*4" score="57" minlen="10"/>
        <type type="string"/>
    </generator>
</generators>

The affect of the elements can be shown in the Red-Gate Data Generator dialogue shown below

Conclusion

The techniques described here certainly work well with SQL Management Studio and the two Red-Gate tools described here.  I have also had success using the technique with other 3rd party tools

Being able to share templates, snippets and data generator configurations within a team environment should give a number of advantages

• Consistency in working environments
• Reduced workload through collaboration
• Potentially offering automation of certain tasks.

Although I have barely scratched the surface of Red-Gate Data Generator I believe that it has a huge potential for collaborative work, particularly in light of its primary purpose for generating test data.

I have it firmly in my sites for further investigation as I believe that in conjunction with Red-Gate SQL Test its use could really push the boundaries of what can be achieved in pushing test driven development practises into the data tier.