D.R.Y. with SQL ScriptsPublished 28 March 2014 3:54 pm
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.
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?