Shortcut: Snippets in SQL Server Management Studio

Have you ever started to create an object using T-SQL in SQL Server, and thought, what’s the right syntax for this? I’ve worked with SQL Server since 1992 (version 4.2) and yet almost every time I go to create a function, I have to spend a few moments thinking about what the correct syntax is, because there are different types of functions (scalar vs table-valued, inline vs multi-statement).

SQL Server Management Studio has had templates for a long time, and they are useful. In fact, you can create your own. I’ll show that in another blog post soon.

But what I wanted to focus on today, are the ones that were added a while back, yet most people seem to be unaware are there. And that’s snippets.

At present, one of the easiest ways to get the basic syntax for creating an object is to use a snippet.

In a query window, right-click a blank area and note the option for inserting a snippet (you can also do this by Ctrl-K, then Ctrl-X):

Then select the type of object you want:

In this case, let’s choose Function, and then we’ll see the types of functions that are available:

Let’s choose Create Inline Table Function, and SSMS fills in a skeleton for us:

Great. But don’t get carried away with your mouse just yet. Notice that the template has placeholders that you can replace and the in this case, the schema is pre-highlighted. So I can just type the schema name, and hit tab, then type the function name, and hit tab, and so on until all placeholders are complete. Note that it has also stubbed out a SELECT statement for us, and as we change the parameter names, it changes the names in the SELECT as well..

I’ve no doubt that the product team thinks I’m a bit pedantic about these things, but given we’ve been encouraging people to use semicolons as statement terminators since 2005, I wish they’d include that in the statement that they’ve provided. I must ping @sqltoolsguy about that :-)

But snippets are wonderful and help to avoid that “what’s the syntax for this” issue.

2017-11-16