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.
|
1 2 3 4 5 6 |
-- Use Code Snippets to generate the syntax of your Non Clustered Index CREATE UNIQUE NONCLUSTERED INDEX IX_TableName_Col1 ON dbo.TableName (column_1) |
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.
|
1 2 3 4 |
SELECT COUNT(1) FROM [AdventureWorks2008].[Person].[Person] |
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.
|
1 2 3 4 5 |
-- Running a custom script against all my database servers SELECT COUNT(1) AS 'Statistics RowCount' FROM [perf].[Statistics] |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- ============================================= -- Create database template -- ============================================= USE master GO -- Drop the database if it already exists IF EXISTS ( SELECT name FROM sys.databases WHERE name = N'<Database_Name, sysname, Database_Name>' ) DROP DATABASE <Database_Name, sysname, Database_Name> GO CREATE DATABASE <Database_Name, sysname, Database_Name> GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- ============================================= -- Create database template -- ============================================= USE master GO -- Drop the database if it already exists IF EXISTS ( SELECT name FROM sys.databases WHERE name = N'Statistics' ) DROP DATABASE Statistics GO CREATE DATABASE Statistics GO |
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.
|
1 2 3 4 5 6 |
-- Obtain metadata of the database using Information Schema view select count(*) from INFORMATION_SCHEMA.TABLES select count(*) from INFORMATION_SCHEMA.ROUTINES select count(*) from INFORMATION_SCHEMA.VIEWS |
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.
Samir is a frequent speaker at conferences such as PASS Summit, IT/Dec Connections, CodeStock, SQL Saturdays and CodeCamps. He is the Co-Chapter Lead of the Steel City SQL Server UserGroup, Birmingham, AL. He is the author of www.dotnetvibes.com
View all posts by Samir Behara
- What’s new in SQL Server Management Studio 17.4; SQL Vulnerability assessment and more - December 26, 2017
- Review of SQL Cop for SQL unit testing - September 29, 2017
- Querying Microsoft SQL Server 2012/2014 – Preparing for Exam 70-461 - September 8, 2017