It’s time for T-SQL Tuesday again, and this time Allen White (@SQLRunr | blog) is asking for your tricks. If you want to participate, read Allen’s post and learn how.
The question this month is: What T-SQL tricks do you use today to make your job easier?
My Tricks
I don’t have any great whiz bang tricks in T-SQL, and I’m sure there are more than a few people that can out-code me with their. However I do like to make my job easier, and so I have a couple of administrative tricks for use with your T-SQL environments. These are the ways that I save time, and work more efficiently.
The thing that has helped me most often in my career is to keep little snippets of code handy to that I make few mistakes and save time. These days I do that quite often with SQL Prompt, a third party tool from my employer. It basically implements intellisense for SSMS, but more importantly, it gives me shortcuts.
However the biggest advantage to me is the Snippets in Prompt. There’s a feature that allows you to type a shortcut and then press “Tab” and have that shortcut replaced with a longer section of text. For example. I have this shortcut:
If I type “zqd” in SSMS, and then hit tab, the T-SQL in the “code” box above appears. There are a few very frequently used slices of code that I can insert like this, without taking my hands off the keyboard, which is very handy. There’s even a whole snippet manager in Prompt that has pre-defined, and custom, snippets.
The most often one I use is “ssf”, which inserts this:
SELECT TOP 10 * FROM
So do you need to buy SQL Prompt? No, but if you do, tell them I recommended it so my boss with be happy and maybe send me a nice bonus next Christmas.
A very similar functionality is in SSMS. I actually used to heavily use templates in the old Query Analyzer days of SQL Server 7/2000 and this has continued in Management Studio with the Template Explorer
I can drag a template from the explorer on the right into the code window and the code appears. I’ve pulled in the backup template. You can even add your own:
It’s easy to do, and you can read more about Template Explorer in BOL.
However if you’re like me, you move around, you use VMs for coding, and you want to be sure that your tools are on all these machines. There are a few ways to do this:
- portable drives
- cloud sevices
I guess these are both the same thing, just implemented differently. I’ve used both ways, and while I do carry some flash drives, and hard drives, with various items on there, I find that I can never quite keep these up to date, and they’re really emergency drives for me in the event I don’t have connectivity.
The primary way that I manage mode snippets, templates, etc. is by putting all my code in centralized places. For Prompt and SSMS, these locations are known, and while configurable, I stick with the defaults. For me this means I have three folders to track:
- SQL Prompt default snippet folder
- SSMS Templates folder
- \SQL in my Documents folder in Windows
All three of these folders are the same on all my machines, and I use a cloud service to keep them in sync. For me, I have two difference services in play, mostly for testing, and I see little difference between them. I have Live Mesh, a Microsoft service, for some folders, and DropBox for others. From what I’ve seen, they both work essentially the same, though DropBox is a little smoother for me with the Apple integration of some apps. That probably doesn’t matter for most of you, but it’s a difference. Live Mesh works on my Macbook, but not on the iPhone.
There are other cloud services, and you can choose the one that works well for you, but I highly recommend you have a script library, as well as a snippet/template library, and you use a cloud service to be sure you can access those files if you are away from your primary machine. You might be surprised how handy this is when working on a server or remote machine.
That’s my T-SQL Tuesday trick for T-SQL, better script management to make your work easier.
