Samir Behara
Machine generated alternative text: SQLQuery9sql select select select 100% Results I...IPIN count(*) count(*) count(*) Messages from from from INFORMATION INFORMATION INFORMATION SCHEMA. SCHEMA. SCHEMA. TABLES ROUTINES VIEWS SQLQuery8.sql select select select 100% Results (l...XSQLEXPRESS.master* -a X count(*) from INFORMATION SCHEMA. TABLES count(*) from INFORMATION SCHEMA. ROUTINES count(*) from INFORMATION SCHEMA. VIEWS Messages (No column name (No column name) (No column name) LEXPR (No column name) 108 (No lumn name) (No column name)

Increase your Coding Speed in SQL Server Management Studio

December 23, 2016 by

Every developer wants to be productive and get more things accomplished during their day to day work. Given a choice between working harder and working smarter, you will most likely choose the latter. But the big question is – How do you boost your productivity? How do you reduce your development time? How do you increase your coding speed?

Be it a T-SQL developer or DBA, we spend most of our time working in SQL Server Management Studio. In this article, we will learn a number of tips and tricks which will enhance our productivity and efficiency in SSMS IDE. We will explore ways to stop doing manual repetitive tasks, use shortcuts, type less and generate more lines of code in minimal time.

In my previous article, we looked at the new and improved features of SSMS 2016. If you have not gone through it, I would highly recommend you to read it, install SQL Server Management Studio 2016 and start leveraging the productivity enhancements – SQL Server Management Studio 2016 Productivity Enhancements

Type less and generate more code

SSMS provides a cool feature called ‘Code Snippets’ which can help you to considerably increase your coding speed. Code Snippets are ready-made snippets of code, which you can insert into your T-SQL code. This is a great time saver, since there is no need to remember any syntax while you are creating your stored procedures, tables, indexes and more. You can also create your own custom snippets and add to the pre-defined snippets available in SQL Server.

In the SSMS IDE, you can bring up the code snippet menu by just clicking on CTRL K + X.

You can then navigate through the folders and select the snippet you want to use.

 Based on your selection, you will now see the snippet for creating a new non-clustered index.

You can also press CTRL K + B to bring up the Code Snippets Manager, where you can see all the code snippets and manage them.

Get Row Count of tables without executing a T-SQL script

Generally to find the number of records in a table, we execute a T-SQL query and get the information.

But SSMS provides an out of the box feature, which displays the Row Count for all tables in a database – without writing any explicit queries. The Object Explorer Details window is one feature which very few developers leverage during their day to day work. This provides a user interface which lists down all the objects in a server and additional information like Row Count, Data Space Used, Index Space Used and more.

The Object Explorer Details is not visible in SSMS by default. You can click F7 or just navigate to View -> Object Explorer Details to view this window.

Another hidden feature regarding this details window is that the columns in the Object Explorer window are customizable and you can add extra columns from options highlighted in green below:

Save connection information of frequently accessed servers

When you are working in your project, you tend to have a number of servers which you need to constantly shift between – Local/DEV/QA/MOCK/PROD. The easiest way to save these frequently accessed servers is by storing the connection information of the servers using a feature called ‘Registered Servers‘.

To view this window in SSMS, you can navigate to View -> Registered Servers. Then right-click on the ‘Local Server Groups’ and click on ‘New Server Registration’.

Now you can go ahead and register the server by entering the connection details. Note that you can also give a custom name for the registered server.

I have registered all my frequently used servers in my project under ‘Local Server Groups’ folder, as you can see in the screenshot below.

Execute a SQL Query against multiple servers at the same time

Once you have registered all your frequently used servers using the ‘Registered Servers’ feature, all your servers appear under the Local Server Groups. You can also create your own groups and have selected servers under it. One great advantage of creating such groups is that it gives you an ability to execute a single TSQL query against all the database servers in that group.

This saves lot of developer’s time since you do not need to connect to different database instances one by one and execute the same query multiple times. All you need to do now is right click on ‘Local Server Groups’ folder and click on New Query option.

In the query editor, you can type the TSQL query and run it. The Results pane, will display the query results fired against all the servers in that group.

 

Save time by leveraging pre-defined SQL scripts

While writing a T-SQL query, if you don’t remember the syntax, you spend time to search for an online article or refer a book to figure out the syntax and formulate the query. Google never fails us!

Most of the developers are not aware of an inbuilt feature in SSMS called ‘Template Browser‘ – where it displays a number of templates out of the box. These templates contains placeholders/parameters that help you to create a number of database objects like tables, indexes, views, functions, stored procedures and more in no time. The templates are updated with each SQL Server release, so you can rest assured that you are working with the up-to date syntax.

You can open this window by clicking on ‘View’ menu and then clicking on ‘Template Explorer’.

Now if you click on say ‘Create Database’, it will open up the respective template in the query window.

You can replace the parameters by pressing CTRL+SHIFT+M, which pops up a new window where you can specify the value for the parameters.

Once you replace the parameters with valid names, you will get the final script ready for execution.

Maximize your workspace with multiple code windows

Having multiple screens is a big productivity boost, since you no longer need to constantly flip back and forth between multiple programs. You can avail the benefits of side by side comparison and can also share data between screens easily. Having said that in SSMS, you can use ‘Horizontal or Vertical Tab Groups‘ to maximize your workspace by using separate code windows, in the process leveraging the same benefits of having multiple screens.

When you have multiple tabs open, you can right click on any of the tab and choose between Horizontal vs Vertical Tab Group, as per your preference.

Each code window can establish an independent connection to a SQL Server instance and hence you can execute the same query against different server instances and compare results. You can also compare script files or their result side by side.

Use Keyboard Shortcuts to increase your efficiency

There are multiple benefits of using Keyboard shortcuts over mouse. For repetitive day to day actions, you can achieve a desired functionality much faster using keyboard shortcuts and with just couple of keystrokes. There is a slight learning curve involved to memorize the keyboard shortcuts, but once you start using your favorite keyboard shortcuts on a day to day basis, it becomes a habit and help you to be more efficient.

Generally while working in SSMS, we have multiple query windows open. By pressing CTRL+TAB, a dialog box opens containing all the open tabs and you can very conveniently switch between them. This is one shortcut which I use daily.

I have listed below few of my favorite SSMS shortcuts which you can use to enhance your productivity in the IDE –

Shortcuts Action
CTRL+R Toggle between displaying and hiding Results Pane
CTRL+N Open new query tab with current database connection
CTRL+L Display estimated execution plan of the query
SHIFT+ALT+ENTER Toggle between Code Editor being displayed in Full Screen
CTRL + ] Navigate to the matching parenthesis
CTRL+K+X Insert SQL Code Snippets
CTRL+SHIFT+U Change text to upper case
CTRL+SHIFT+L Change text to lower case
CTRL+K+C Comment selected text
CTRL+K+U Uncomment selected text

To view the complete list of SQL Server Management Studio Keyboard Shortcuts, please refer this MSDN link – SQL Server Management Studio Keyboard Shortcuts

Leverage Regular Expressions for faster find and replace

Sometimes find and replace operation can become a very tedious process, especially with larger queries/text where lot of manual effort might be required. Regular Expressions are a blessing in disguise in such scenarios. It allows you to parse through large text, find a particular matching pattern and replace them in a breeze. You will not realize how powerful and beneficial this tool is, until you start using it.

To enable usage of regular expression, click CTRL+SHIFT+H to open the Find and Replace dialog box. Then you can click the ‘Expression Builder’ icon next to Find What field. It will display the most commonly used expressions, which you can leverage to find specific patterns.

You can find more details about Searching with Regular Expressions in the MSDN link Search Text with Regular Expressions

Conclusion

Gaining a superlative understanding of the IDE and its features goes a long way in increasing one’s productivity. In this article, we went through a number of SSMS tips and tricks to increase the coding speed of a TSQL developer. Hopefully you have learned some useful techniques to take back with you and apply in your day to day work. For a 3rd party tool to further enhance coding productivity, see ApexSQL Complete.

See more

To boost your SQL Server development productivity, check out Free SQL Server Management Studio add-ins.

References

 

Samir Behara

Samir Behara

Senior Developer at EBSCO Industries
Samir Behara has a Bachelor Degree in Computer Science and has been part of the IT Industry since he graduated in 2006. He has worked on large-scale enterprise applications involving complex business functions, web integration, and data management in various domains like Insurance, Manufacturing, and Publishing.Samir builds software solutions using cutting edge Microsoft technologies, primarily C# and SQL Server.

Amidst working in a wide range of technologies - both front end and back end, one thing which has not changed is his love for SQL Server. He is passionate about developing his SQL skills, contributing to the SQL Server community and keeping in pace with the new technologies and advancements. He is the organizer of the Steel City SQL Server Users Group – which is the Birmingham, AL chapter of PASS. He is the author of www.dotnetvibes.com

View all posts by Samir Behara
Samir Behara
SQL Server Management Studio

About Samir Behara

Samir Behara has a Bachelor Degree in Computer Science and has been part of the IT Industry since he graduated in 2006. He has worked on large-scale enterprise applications involving complex business functions, web integration, and data management in various domains like Insurance, Manufacturing, and Publishing. Samir builds software solutions using cutting edge Microsoft technologies, primarily C# and SQL Server. Amidst working in a wide range of technologies - both front end and back end, one thing which has not changed is his love for SQL Server. He is passionate about developing his SQL skills, contributing to the SQL Server community and keeping in pace with the new technologies and advancements. He is the organizer of the Steel City SQL Server Users Group – which is the Birmingham, AL chapter of PASS. He is the author of www.dotnetvibes.com View all posts by Samir Behara

10,479 Views