28 March 2014

D.R.Y. with SQL Scripts

Developers strive to write well-tested, reusable code with well-defined interfaces so that when they need to update the functionality, they need do so in one place only. It is the principle of ‘Don’t Repeat Yourself’ (D.R.Y.).

However, it is common for developers to be poor at applying D.R.Y. to their own past work. When it comes time to implement some complex new routine, a faint bell rings in their mind…didn’t I write something similar for that CRM project? What year was that…? If a brute-force search through their chaotic script archive doesn’t unearth it, within a few minutes, they then roll up sleeves, crack knuckles and set about writing it again from scratch, convinced they will do a better job of it this time, anyway: And, after all, it’s fun.

However, what if you really don’t have time to write the code from scratch, or need some pointers to get started? You might trawl a few of your favorite blogs, or find something on Stack Overflow. After all, many developers and DBAs blog all sorts of snippets and scripts, suggesting hopefully that they may be useful to others, but admitting that their main motivation is to know where to come the next time they need it themselves!

Another option, when in need of SQL code, is to search a public script archive such as the one on SQLServerCentral.com. This always used to be a tricky operation. If, for example, we wanted a string-splitting function, we’d need to type into Google something like ‘site:www.sqlservercentral.com/Scripts/ list split‘. Now it is so much easier, with the addition of a small SSMS plug-in called SQL Scripts.

Let’s say you’re browsing the daily SSC newsletter and spot a potentially useful script, but don’t have time right now to look deeper. Simply add the script to your SSC briefcase and, with SQL Scripts installed, you can access all the scripts in your briefcase directly from SSMS, from the “favorites” menu.editorial_1.png

In addition, you can search the whole archive directly from within SSMS, as well as add scripts to your briefcase and to the archive as a whole.

It has the potential to be a very useful tool. At least, it’s a good start. I’d rather like the ability to filter scripts by tag, rather than just perform a blanket search. While sorting by “star rating” is somewhat useful, I’d rather like a more meaningful indicator of quality. Perhaps we should follow Phil Factor’s example; the SSC community could set up a minimal test framework, and test the top x scripts in each category/tag. Those that perform and scale adequately could get some sort of special marker in the archive. I’d also like an indication of the versions of SQL Server on which each script will run.

If you’re an occasional or frequent miner of the SSC script archive, we’d love you to try out SQL Scripts and let us know what you think. How much might it help you reuse the code of others, as well as find and reuse your own? What ideas do you have for additional features?

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


  • Rate
    [Total: 0    Average: 0/5]

Tony Davis is an Editor with Red Gate Software, based in Cambridge (UK), specializing in databases, and especially SQL Server. He edits articles and writes editorials for both the Simple-talk.com and SQLServerCentral.com websites and newsletters, with a combined audience of over 1.5 million subscribers. You can sample his short-form writing at either his Simple-Talk.com blog or his SQLServerCentral.com author page.

As the editor behind most of the SQL Server books published by Red Gate, he spends much of his time helping others express what they know about SQL Server. He is also the lead author of the book, SQL Server Transaction Log Management.

In his spare time, he enjoys running, football, contemporary fiction and real ale.

View all articles by Tony Davis