Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Templates and teamwork

By David Poole, (first published: 2008/05/20)

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

Version File Extension Template location
SQL 2000 / SQL Query Analyser TQL C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer\
SQL2005 SQL Static within the users profile under \Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql\
SQL2008 & 2008R2 SQL Static within the users profile under \Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql\
SQL2012 SQL

I'm running SQL2012 on a Windows 7 laptop so your location may vary but within the user profile the location is

\AppData\roaming\Microsoft\SQL Server Management Studio\11.0\Templates\SQL

Note that the location now names SQL Server Management Studio explicitly and not SQL server.

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.

View menu option to bring up templates

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

View menu option to bring up templates

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

Switch Purpose
c Copy even if an error occurs
e Copy subfolders
i If a destination doesn't exist for multi-file copies assume the destination should be a folder.
y If the destination file exists overwrite it without prompting.

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

Red-Gate Data Generator tags
Tag Purpose
type Tells Data Generator which data generation technique to use.  In this case we want it to use an input file as the source of the data to be chosen

name

description

Concatenated to show in the "Generator" drop down box highlighted with the red elipese.
category Generation schemes are put into categories.  In this case, just like the US Cities on which I based my UK Counties list, I wanted to use the Geographical category
property name="FileName" I could have used any filename I chose however it makes sense to keep the txt and xml filenames a matching pair for ease of maintenance.
matches

Describes basic rules for allowing Data Generator to choose appropriate data generation schemes automatically.

Note that the default installation of Data Generator already has a ruleset for AddressLine fields but in the absence of a higher scoring rule both County and AddressLine4 will be populated with contents read from my UKCounties.txt file.

Note that Addr.*4 means any field name that begins with Addr and ends with 4.

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.

Total article views: 8182 | Views in the last 30 days: 30
 
Related Articles
ARTICLE

SSMS Shared SQL Templates

This article describes how to use the built in repository for SQL Server Management Studio Templates...

FORUM

Management Studio Templates that wont go away!

I get rid of the templates and they keep coming back!

FORUM

Microsoft SQL Server Management Studio Question

Microsoft SQL Server Management Studio Question

FORUM

SSMS and Template Explorer

SSMS Templates

FORUM

Licensing SQL Sever Management Studio.

Licensing SQL Sever Management Studio.

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones