Nobody would want to shy away from some help. This is second to human nature and we never try to talk it out explicitly. When at work, if we can surely get help from our colleagues when it comes to driving the deadlines and completing it ahead of time. Keeping things till the last moment will only bring our heart rate higher and the anxiety to finish will sometimes make us give a sub optimal output. In this blog, we will try to look at how some of the basic, yet hidden features of SQL Server Management Studio can be used effectively.
If you ask us, this is a nifty tip when working with SQL Server 2012 onwards. If you are an user of Visual Studio, this would be so much familiar to you.
Creating a T-SQL Snippet
The first basic T-SQL snippet we can invoke is a BEGIN, IF or a WHILE block using the T-SQL snippet. To invoke the same use CTRL+K, CTRL+S. Once we use these key combinations, we will be presented with a small dialog to work.
If we bring the IF condition or the WHILE condition, we will be presented with a TSQL block as a template. We need to replace the “condition” section and we will be done with the block.
Surrounding our T-SQL code via template
If you have a requirement to quickly add an IF block around a code that we have already built, what is the easiest way. Well, feel free to use this code snippet construct. The shortcut key remains the same – CTRL+K, CTRL+S. Select the code block and use this shortcut. Below is a representation of initial and final output.
Enter the valid condition for the code block and move on. This is the quickest way to achieve this task.
Power of “Insert Code Snippet”
If you really ask me, this is a powerful handy tip if you are a developer or a DBA. Simple tasks can be quickly created using this method. Tasks like creating a table, creating a function, creating a login, creating a stored Procedure, creating a trigger, creating a synonym and many more.
The shortcut for this is similar, CTRL+K followed by CTRL+X. The image below shows the basic task we have done using SQL Server Management Studio.
This will invoke yet another level of options and then the template is dropped. A typical output of a template for Stored Procedure which will use an OUTPUT parameter is shown below:
The next step is to change the parameters and then create our stored procedures. In the above example, we can see a number of values are in “Yellow highlighted”. Just type the changes and press TAB key. We will be moved to the next parameter.
Conclusion
The more one works on SQL Server and SQL Server Management Studio, I recognize the lesser we know of the tools that we have with us. Productivity is discovering these hidden gems and then using them proactively in our environments from time to time is the key. This blog was just in that direction to bring out some of these lesser known features.
|
|
About Pinal Dave Pinal Dave works as a Technology Evangelist (Database and BI) with Microsoft India. He has written over 2000 articles on the subject on his blog at https://blog.sqlauthority.com. During his career he has worked both in India and the US, mostly working with SQL Server Technology – right from version 6.5 to its latest form. Pinal has worked on many performance tuning and optimization projects for high transactional systems. He has been a regular speaker at many international events like TechEd, SQL PASS, MSDN, TechNet and countless user groups. |
Click to learn more about Embarcadero database tools related to this post and more from Pinal Dave: