Let me start the new year with some nifty tips with SQL Server Management Studio 2012. I am a big time fan of using keyboard short cuts and if we are writing some code, how can I get a template for some common activities instantly for creating index, building loops, tables, UDFs, views or admin activities like new logins? Guess what we have all these already available in your finger tips.
1. TSQL Template
To start with, if you want a template with BEGIN, IF or WHILE block use the Ctrl+K followed by Ctrl+S. This bring us the nice little option to pick from. Just as you type the command you want, it will automatically get the cursor there. In this example we have typed WHILE and now our SSMS window will look like below:
Select the While command, give us an template to start work. Now the screen looks like:
Just go about changing the condition and now we have things ready just by few short cut keys.
2. Surround TSQL Template
If you did notice in the first figure it says “Surround With” when we were selecting our template. There is a reason for this. Now assume we have an INSERT Statement and we want to add and condition such that if the condition succeeds then the Insert must happen. How can we achieve this task.
- Select the INSERT Statement. Like top half of below diagram.
- Press Ctrl+k followed by Ctrl+s.
- Select the IF clause.
- Add the condition and finish task.
If you thought this was cool, wait you have not seen the power of SSMS yet.
3. Insert Snippet
Earlier we mentioned there are a bunch of snippets preconfigured and we can use them too if required. This can be invoked by using the Ctrl+k followed by Ctrl+x. To play around with this, let us get back to our management studio and invoke this to create an Index script.
For our demonstration we will select the “Create Index Basic” script and it will look like below.
This is the quick script and from the screen it is evident that we need to enter 4 different values (Name of index, schema of object, Object name and finally Column Name). The quick navigation between each of these 4 template name placeholders is to use TAB.
4. invoke Snippet from Query Window
The quick and easy way to invoke this from query window is to right click on the query window and use the two options for snippets.
5. Invoke snippet from Toolbar
You can goto Tools –> Customize and under Toolbars check “Context Menus” option and click Ok. Now we can access the same from the toolbars just like the figure below:
Though we are invoking the Snippet from Query Window or via toolbar, the behavior of these still are the same as discussed before.
Hope you enjoyed reading this blog post and do tell me if you find this useful. We have not covered how to create your own snippet. But that will be for another day in a different blog post.
This entry was posted on Wednesday, January 2nd, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.