This month's T-SQL Tuesday is being hosted by Allen White (@SQLRunr) and is about sharing your T-SQL tips and tricks. Since I know many people will share their T-SQL magic and wizardry, I thought I would share a couple of productivity tips – so still T-SQL related, but more how to take advantage of some features in SSMS.

Drag column list onto your query window

Whenever I give my Bad Habits to Kick presentation, I remind people that – even if they're not using 3rd party code completion tools (many of which offer SELECT * expansion) – there's little excuse to use SELECT *. This is because Management Studio makes it very easy to list out all of your columns in one motion. I'm always amazed when I ask how many people didn't already know about this – and most times it's more than half the room.

The process is quite simple – expand your table, click on the Columns node, drag it onto your query window and let go:

Now, it's probably not in the exact format you want, it won't properly delimit any [keyword] or [reserved word] you use for a column name, and you'll have to do this multiple times when you're performing a join (and you'll have to add your own table/alias prefixes to each column as well). But I'd rather spend time using TAB and ENTER to correct formatting than to try and type out all the column names individually.

Use snippets

If you find yourself writing the same type of code over and over again, the new snippets feature in SQL Server 2012 will likely be very useful for you. What does this have over templates? For one, I find it much easier to use once you have your snippets created. There is no hokey VB6-style dialog to perform token replacements, and you can actually store all your custom snippets in a network location – so if you want to enforce coding standards you have a fighting chance by pointing everyone's SSMS at the same snippet location. I'll be honest, though, setting up the template is a bit of a pain – you need to hand-massage XML manually because as of yet there is no easy way to generate these from within Management Studio. But once they're in place, they're very easy to work with. Just right-click within the query window, choose "Insert Snippet", and follow the rabbit:

One of the pieces of code I write a lot for administrative tasks (or for helping folks out in the wild) is a cursor. I tend to have a hard time memorizing the options I always try to use to make a cursor as efficient as possible (something else I cover in my Bad Habits to Kick presentation). The snippet eliminates this problem. Once I've selected the "Declare Cursor" snippet I've saved, I get the following in my query window:

You'll notice in the diagram that "c" is currently highlighted and the cursor is there; all other instances of "c" are lightly outlined. I can type any name I want to overwrite "c" and, once I hit tab (which moves me to the next token), it will replace all instances of that token with the new name I've typed. So this is a little easier to work with than the floating window in templates, though you lose this easy editing capability if you start doing other things with the script first (need to remind myself to file a bug or suggestion about that). Changing "c" to "x" throughout this code sample took two keystrokes, and I'm ready to add the actual query:

I plan to write a much more thorough blog post on the snippets feature, because I think it will become a pretty popular time saver once the latest version of Management Studio becomes more common on the desktop. In the meantime, if you're already playing with SQL Server 2012 RC0, I highly recommend you check this feature out!

Avoid dynamic SQL in generated scripts

I see a lot of people complain that when they script objects from Management Studio the object text ends up in dynamic SQL. I don't blame them – this makes it very cumbersome to read, never mind modify, the object text before copying, running or saving the script.

In SQL Server 2008 R2, this is controlled by the option Tools > Options > SQL Server Object Explorer> Scripting > Include IF NOT EXISTS clause:

In SQL Server 2012, the option has changed slightly to Tools > Options > SQL Server Object Explorer > Scripting > Check for Object Existence:

Why did it change? Because, depending on the action, it actually performs an IF EXISTS check (e.g. for a DROP or ALTER), not always an IF NOT EXISTS check (for a CREATE). Some background in the following Connect items:

• Connect #242799
  Connect #264310

  Connect #624075

(As an aside, I think it's funny that they call the options category "SQL Server Object Explorer" – good thing they're specific, because you might get it confused with the Lego Object Explorer, Oracle Object Explorer or Windows Explorer Object Explorer.)

In any case, when these options are set to True, the scripts you generate often yield dynamic SQL, and even when they're set to false they still sometimes perform the check for existence (specifically in 2008 R2). Here is a brief wireframe of the options and how they affect each scripting method:

Note that with the "DROP and CREATE" option, the existence check for the CREATE is unnecessary – after all, if the object already exists, the script should have just dropped the object (though both checks may have failed if there is an object with the same name that is *not* a procedure). In general, most people won't want to perform a DROP and CREATE anyway – they lose dependencies and permissions when they do this (though permissions can be scripted with a different option in the same category). Until they develop "CREATE or REPLACE" functionality (please vote and comment!), ideally you should have a script that creates a stub for the object only if it doesn't already exist, then runs an ALTER – then you don't have to re-apply permissions, you don't have to worry about whether the object already exists, and as an added bonus you retain dependency chains. So your scripts can look like this (still using dynamic SQL, but it's very simple and doesn't present parsing or reading issues):

IF OBJECT_ID('dbo.proc_name') IS NULL
BEGIN
    EXEC sp_executesql N'CREATE PROCEDURE dbo.proc_name AS SELECT 1;';
END
GO
ALTER PROCEDURE dbo.proc_name
AS
... -- actual script body from source control or elsewhere goes here

Unfortunately there is no way to train SSMS to do this today, unless you write a scripting add-in.

Wrapping up

I hope that at least one of these little tricks helps you in some way. It's always fun participating in T-SQL Tuesday, even if I'm often trying to bend the rules a little bit.