SQL Server Management Studio 2016生产力增强
问题陈述 (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.
在SQL和.NET环境中工作的开发人员必须体验过Visual Studio IDE和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.
我自己是一个全栈开发人员，与Visual Studio IDE即时提供的功能相比，我总是觉得SSMS缺少许多基本功能。 由于SSMS与SQL Server Core引擎紧密耦合，因此在一段时间内，IDE承担了相当多的技术债务。
解析度 (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团队终于听到了人们的声音，现在已经将SSMS与基础SQL Server引擎分离了。 这是个好消息，因为这意味着它将发布更多版本，以合并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.
SQL Server Management Studio现在使用Visual Studio 2015隔离的外壳。 从SQL Server 2008 – SQL Server 2016开始，此版本的SSMS可以与所有SQL Server版本一起使用。SSMS – 2012和2014的早期版本基于Visual Studio 2010 Shell。 随着现在由Visual Studio 2015提供支持的SSMS 2016，用户界面看起来与Visual Studio 2015非常相似，并带来了很多工作效率的提高。
In this article, we will look at the new and improved IDE features of SSMS 2016.
在本文中，我们将介绍SSMS 2016的新增功能和改进的IDE功能。
SSMS is an independent web installer and can be downloaded from – https://msdn.microsoft.com/en-us/library/mt238290.aspx
SSMS是一个独立的Web安装程序，可以从以下网址下载： https://msdn.microsoft.com/zh-cn/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.
安装并打开SSMS 2016后，我们注意到的第一件事是标准工具栏左上方的新导航按钮。
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.
这将允许用户在各个选项卡/脚本之间向后导航（CTRL +-）和向后导航（CTRL + SHIFT +-）。 我发现此功能非常有用，因为通常，我最终会在许多选项卡中编写查询，而拥有这些新的导航按钮可帮助我在脚本窗口之间快速移动。 保存图标现在是深蓝色。
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.
现在，总体SSMS 2016给用户的感觉与Visual Studio 2015非常相似。关于向后导航按钮的注意事项是，它具有一个下拉列表框-包含所有导航历史记录的列表。
自动检查更新 (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.
SSMS 2016具有内置的通知系统 ，可以在有新版本的更新时提醒用户。 每当您启动SSMS时，它都会自动检查更新，并向用户弹出敬酒通知，通知您需要更新SSMS。
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.
当您单击通知时，它将打开一个模式窗口，显示已安装的SSMS组件版本和可用的最新版本。
Clicking on the ‘Update’ button, redirects you to the Microsoft SSMS Download page – https://msdn.microsoft.com/en-us/library/mt238290.aspx
单击“更新”按钮，将您重定向到Microsoft SSMS下载页面– https://msdn.microsoft.com/zh-cn/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.
下载并安装exe文件后，您将需要重新启动计算机以完成设置。 之后，当您打开SSMS并检查更新时，应该会看到类似以下的屏幕，确认SSMS是最新的。
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.
代码编辑器的改进可以极大地提高开发人员的工作效率。 在SSMS 2016中，键入T-SQL代码时，增强的滚动条在垂直栏上显示不同的颜色。
Below is the significance of the various colors:
以下是各种颜色的含义：
- Red – Syntax Errors 红色–语法错误
- Blue – Current Cursor Position 蓝色–当前光标位置
- Green – Saved Changes 绿色–保存的更改
- Yellow – Unsaved Changes 黄色–未保存的更改
提示关闭时保存未保存的TSQL查询窗口 (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中工作时，我们通常倾向于将多个即席查询写成许多选项卡。 当我们要关闭SSMS时，它会弹出一个对话框，询问是否要保存更改，如果我们不想保存SQL文件，可能会有些多余。
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.
现在，SSMS 2016使您可以取消选中此选项，并且在关闭SSMS时不会提供提示。 话虽如此，我强烈建议您明智地使用此功能-因为通过禁用此提示，您很有可能会丢失未保存的工作。
快速启动 (Quick Launch)
Quick Launch greatly helps in making the developers productive in the IDE by helping them to search features and options inside SSMS.
快速启动通过帮助开发人员在SSMS中搜索功能和选项，极大地帮助他们提高了IDE的生产率。
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.
您现在可以通过一种便捷的方法快速导航到任何选项/命令，而不必记住任何快捷方式或进入SSMS菜单。
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.
在SSMS IDE的右上角，您将看到一个文本框，即“快速启动”，它将搜索SSMS菜单项和选项。
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.
您需要做的就是在“快速启动”框中输入有关要在SQL Server Management Studio中访问的任何功能的搜索字符串，然后您将看到结果。
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.
SSMS 2016为我们提供了在两个颜色主题之间进行切换的选项–蓝色和浅色。
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.
即使SSMS使用Visual Studio 2015 Shell，它目前也不支持非常流行的深色主题。 Microsoft建议他们在将来的版本中启用深色主题和高DPI支持。
执行计划窗口的改进 (Improvements to Execution Plan window )
SSMS now allows you to zoom the contents of the execution plans for additional clarity.
现在，SSMS允许您缩放执行计划的内容以更加清晰。
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.
通过使用CTRL +鼠标滚动，您可以增加或减少整个执行计划的显示。 另外，为了方便查看，您可以用鼠标拖动执行计划。 这对于查看连接多个表的大型T-SQL查询的执行计划很有帮助。
选项对话框 (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.
在SSMS中，有很多选项在SSMS工具栏中没有明确显示。 “选项”对话框使您可以快速搜索选项，而不必记住如何导航到该特定设置。
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?
您是否曾经遇到过在TEST环境中运行T-SQL查询并且运行良好的方案，但是同一查询在PROD环境中运行会花费更多的时间？ 还是考虑数据库/服务器升级后突然性能下降的情况？ 在这种情况下如何解决？
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引擎如何处理T-SQL查询，我们生成了执行计划，并查看了各种运算符，成本，使用的索引等。 但是，我讨厌这样的事实：在所有这些时间之后，我们仍然必须手动比较执行计划并找出差异-尤其是对于产生大型执行计划的复杂和大型T-SQL。
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.
SQL Server 2016通过引入“ Compare Showplan ”功能为我们提供了帮助，该功能允许并排比较两个执行计划，并在视觉上突出显示两个计划中不同的运算符/属性。 这有助于我们分析查询性能的变化，并Swift缩小性能瓶颈。 它是一个脱机比较，因此，如果您有两个执行计划保存到磁盘上，则可以使用此工具发现差异，而无需主动连接到数据库服务器。
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’.
要使用此比较工具，您需要在SSMS 2016中打开第一个执行计划。然后右键单击，您应该看到新功能-“比较显示计划”。
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.
SSMS 2016将在同一窗口中同时显示两个执行计划。 如果两个计划中的操作员都相同，则会以相同的颜色突出显示操作员（以下示例中为紫色）。 这是一个可配置的选项，您还可以突出显示不同的运算符。
结论 (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
SSMS现在被视为头等公民，在SQL Server版本之外拥有自己的单独版本。 截止到2016年6月1日，SQL Server 2016首次发布之后，每个月都有SSMS版本发布。您可以查看SSMS更改日志以收集有关这些每月版本中发布的新功能和错误修复的信息– https ：//msdn.microsoft.com/zh-CN/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.
无论是T-SQL开发人员还是DBA，我们大部分时间都在SSMS中工作，以便与SQL Server Engine进行交互。 因此，拥有出色的用户体验，肯定会吸引更多的用户，并且在使该产品在大众中流行方面还有很长的路要走。 对SSMS的所有这些改进肯定会继续从热心SQL社区中获得更多的爱和赞赏。
Moreover you do not need to have a SQL Server 2016 license to install SSMS 2016 – it is absolutely free and is all together 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.
此外，您不需要SQL Server 2016许可证即可安装SSMS 2016 –它是完全免费的，并且比整个SQL Server安装程序单独安装。 我强烈建议所有开发人员和DBA将其安装在您的计算机中，并开始利用生产率的提高。
To further enhance coding productivity, try ApexSQL Complete, a free SSMS and Visual Studio add-in.
为了进一步提高编码效率，请尝试使用ApexSQL Complete （免费的SSMS和Visual Studio加载项）。
翻译自: https://www.sqlshack.com/sql-server-management-studio-2016-productivity-enhancements/