Problem Statement
Developers who work in both SQL and .NET world must have experienced the differences between Visual Studio IDE and SQL Server Management Studio IDE.
Being a full-stack developer myself, I always felt that SSMS lacked a lot of basic features when compared to the functionalities which Visual Studio IDE provided out of the box. With SSMS being tightly coupled with SQL Server Core engine, the IDE had quite a technical debt associated with it over a period of time.
Resolution
The SQL Server Team has finally heard people’s voices and has now decoupled SSMS from the underlying SQL Server engine. This is great news since it means that there will be more frequent releases to incorporate new features, enhancements and bug fixes to SSMS.
SQL Server Management Studio now uses Visual Studio 2015 isolated shell. This version of SSMS works with all the versions of SQL Server starting from SQL Server 2008 – SQL Server 2016. The earlier versions of SSMS – 2012 and 2014 were based on the Visual Studio 2010 shell. With SSMS 2016 now being powered by Visual Studio 2015, the user interface looks very similar to Visual Studio 2015 and brings in a lot of productivity improvements.
In this article, we will look at the new and improved IDE features of SSMS 2016.
SSMS is an independent web installer and can be downloaded from – https://msdn.microsoft.com/en-us/library/mt238290.aspx
Navigation Buttons
Once we install and open SSMS 2016, the first thing we notice is the new Navigation buttons at the top left of the standard toolbar.
This will allow users to Navigate Backward (CTRL+-) and Navigate Forward (CTRL+SHIFT+-) between the various tabs/scripts. I find this feature very helpful since, generally, I end up having queries written in a number of tabs, and having these new navigation buttons helps me to move quickly between my script windows. Save Icons are now dark blue.
Overall SSMS 2016 now gives users a much similar feel as Visual Studio 2015. One thing to notice about the Navigate Backward button is that it has a drop-down list box – containing the list of all your navigation history.
Automatic Check for Updates
SSMS 2016 has a built in notification system to alert users when there are updates for new releases. Whenever you start SSMS, it automatically checks for updates and pops up a toast notification to the user informing that an update to SSMS is required.
When you click on the notification, it opens up a modal window displaying the installed versions of the SSMS components and the latest version available.
Clicking on the ‘Update’ button, redirects you to the Microsoft SSMS Download page – https://msdn.microsoft.com/en-us/library/mt238290.aspx
Once you download and install the exe file, you will need to restart your machine to complete the setup. After that when you open SSMS and check the updates, you should be seeing a similar screen as below, confirming that SSMS is up to date.
In case you miss the notification you also have the option to manually check for updates. You can find this option from the Tools menu by selecting “Check for Updates…”
Vertical Scroll Bar Enhancements
Improvements to the Code Editor can make a huge difference to the productivity of a developer. In SSMS 2016, as you type your T-SQL code, the enhanced scroll bar displays different colors on the vertical bar.
Below is the significance of the various colors:
- Red – Syntax Errors
- Blue – Current Cursor Position
- Green – Saved Changes
- Yellow – Unsaved Changes
Prompt to save unsaved TSQL query windows on close
While working in SSMS, we generally tend to have multiple ad-hoc queries written a number of tabs. When we want to close SSMS, it pops up a dialog box asking if we want to save the changes or not, which might get a bit redundant, if we do not want to save the SQL files.
SSMS 2016 now gives you the opportunity to uncheck this option, and no prompt will be provided while closing SSMS. Having said that, I will highly recommend using this feature wisely – since there is every chance that by disabling this prompt, you might lose your unsaved work.
Quick Launch
Quick Launch greatly helps in making the developers productive in the IDE by helping them to search features and options inside SSMS.
Now you need not remember or even know how to enable/disable/modify a particular feature. You need not browse through the menu options trying to locate an option. Quick Launch – the search tool – does it all for you!
You now have a handy way to quickly navigate to any options/commands without having to remember any shortcuts or digging into the SSMS menu.
In the top right corner of the SSMS IDE, you will see a text box — that is ‘Quick Launch’, which searches through the SSMS menu items and options.
All you need to do is to enter a search string in the ‘Quick Launch’ box about any feature you want to access inside the SQL Server Management Studio & you will see the results.
Another nice thing about this feature is that all the searches are ‘contains’ operations – so as you type in your string in the ‘Quick Launch’ box, it will start displaying results at runtime.
Color Theme
SSMS 2016 gives us the option to switch between 2 color themes – Blue and Light.
You can navigate to Tools->Options->Environment->General->Color Theme and select the theme you like.
Even though SSMS uses Visual Studio 2015 shell, it currently does not support the very popular dark theme. Microsoft has suggested that they will have the dark theme and high DPI support enabled in their future releases.
Improvements to Execution Plan window
SSMS now allows you to zoom the contents of the execution plans for additional clarity.
By using CTRL + Mouse Scroll you can increase or decrease the display of the entire execution plan. Also to allow convenient viewing, you can drag the execution plan with the mouse. This is a great help in viewing the execution plans of large T-SQL queries joining multiple tables.
Options dialog
In SSMS, there are a bunch of options which are not explicitly visible in the SSMS toolbar. The Options dialog allows you to quickly search through the options, without having to remember how to navigate to that particular setting.
Again, all the searches are ‘contains‘ operations – so as you type in your string in the Options box, it will start displaying results at runtime.
Compare Execution Plans
Have you ever come across a scenario when you run a T-SQL query in your TEST environment and it ran fine, but the same query takes considerably more time to run in PROD environment? Or think about a scenario when the performance all of a sudden has degraded after a database/server upgrade? How do you troubleshoot in such cases?
To have an in-depth understanding of how a T-SQL query was processed by SQL Server engine, we generate the execution plan and look at the various operators, cost, index used and more. However, I hate the fact that after all this time, we still have to compare execution plans manually and figure out the differences – especially for complex and large T-SQLs, which produces big execution plans.
SQL Server 2016 comes to our rescue by introducing a ‘Compare Showplan’ feature which allows side by side comparison of two execution plans and visually highlights the differing operators/properties in both the plans. This helps us in analyzing the change in query performance and quickly narrowing doing the performance bottleneck. It is an Offline Compare, so if you have two execution plans saved to your disk, you can use this tool to spot the differences and need not be actively connected to the database server.
To use this comparison tool, you will need to open the first execution plan in SSMS 2016. Then right click and you should see the new feature — ‘Compare Showplan’.
Now click on this option and select the second execution plan you want to compare to.
SSMS 2016 will display both the execution plans side by side in the same window. It will highlight the operators in the same color if they are same in both plans (purple in the below instance). It is a configurable option and you can also highlight the operators which are different.
Conclusion
SSMS is now being treated as a first class citizen, with its own separate releases outside the SQL Server release. As of date, there has been SSMS releases every month, following the initial SQL Server 2016 release on 1st June, 2016. You can check the SSMS changelog to gather information about the new features, bug fixes released as part of these monthly releases – https://msdn.microsoft.com/en-us/library/mt588477.aspx
Be it a T-SQL developer or DBA, we spend most of our time working in SSMS, to interact with the SQL Server Engine. Hence having a superior user experience, will definitely pull in more users and will go a long way in making the product popular among the masses. All these improvements to SSMS will surely continue to gain more love and appreciation from the enthusiastic SQL community.
Moreover, you do not need to have a SQL Server 2016 license to install SSMS 2016 – it is absolutely free and is altogether a separate install than the entire SQL Server setup. I would highly recommend all developers and DBAs to have it installed in your machines and start leveraging the productivity enhancements.
To further enhance coding productivity, try ApexSQL Complete, a SSMS and Visual Studio add-in.
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