SQL Server Management Studio 17.2中的新增功能；身份验证方法，脚本选项等

最新推荐文章于 2022-10-15 11:22:13 发布

culuo4781

最新推荐文章于 2022-10-15 11:22:13 发布

阅读量453

点赞数

文章标签：数据库 python java mysql linux

原文链接：https://www.sqlshack.com/whats-new-sql-server-management-studio-17-2-authentication-methods-scripting-options/

版权

SQL Server Management Studio is an integrated graphical interface that is used to configure, manage, monitor and administrate the SQL Server instances hosted on the local machine, on a remote server or in the cloud. It provides us with editing, debugging and deploying environment for the T-SQL, XML, MDX and DMX languages.

SQL Server Management Studio是一个集成的图形界面，用于配置，管理，监视和管理本地计算机，远程服务器或云中托管SQL Server实例。它为我们提供了针对T-SQL，XML，MDX和DMX语言的编辑，调试和部署环境。

With the release of any new SQL Server version that introduces new features and enhancements, SQL Server Management Studio will be also upgraded and enhanced to fit these enhancements, having the same version number of that SQL Server version.

随着任何引入新功能和增强功能的新SQL Server版本的发布，SQL Server Management Studio也将进行升级和增强以适应这些增强功能，并且具有与该SQL Server版本相同的版本号。

For example, in SQL Server 2016, the version number of the SQL Server Database Engine starts with 13.0, and the SQL Server Management Studio also takes the same version number 13.0. SQL Server 2017 was announced now, taking a new version number of 14.0. But the SQL Server Management Studio that comes with the 14.0 SQL Server version has taken a different versioning track, where the last version number for the SQL Server Management Studio released recently is 17.2, rather than using 14.0, which is the version number of the SQL Server 2017. The build number for the last released SQL Server Management Studio is 14.0.17177.0 as you can see in the below snapshot taken from the About choice of the HELP menu:

例如，在SQL Server 2016中，SQL Server数据库引擎的版本号以13.0开头，而SQL Server Management Studio的版本号也为13.0。 SQL Server 2017现已发布，新版本号为14.0。但是14.0 SQL Server版本随附SQL Server Management Studio采取了不同的版本控制轨道，最近发布SQL Server Management Studio的最新版本号是17.2，而不是使用14.0（这是SQL的版本号） Server2017。最新发布SQL Server Management Studio的内部版本号是14.0.17177.0，如您在下面的快照中看到的，该快照是从“ 帮助”菜单的“ 关于”选择中获取的：

The SQL Server Management Studio 17.2 version provides support to all features of SQL Server 2008 through SQL Server 2017, in addition to supporting the Azure SQL Database and Azure SQL Data Warehouse features. SSMS 17.2 can work with SQL Server 2000 and SQL Server 2005 versions, but you may face some problems when working with some features. SQL Server Management Studio 17.2 can be installed on a machine that works with the Windows 7 through Windows 10 and on a server, that works with Windows Server 2008 R2 through Windows Server 2016, making sure that you have installed the latest available service pack for that operating system.

除了支持Azure SQL数据库和Azure SQL数据仓库功能外，SQL Server Management Studio 17.2版还提供对SQL Server 2008到SQL Server 2017的所有功能的支持。 SSMS 17.2可以使用SQL Server 2000和SQL Server 2005版本，但是使用某些功能时可能会遇到一些问题。可以将SQL Server Management Studio 17.2安装在通过Windows 10与Windows 7搭配使用的机器上以及通过Windows Server 2016与Windows Server 2008 R2搭配使用的服务器上，确保已为此安装了最新的可用Service Pack。操作系统。

Similar to SQL Server 2016, the SQL Server Management Studio installation is not included within the SQL Server 2017 shared features installation media. Instead of that, you will find a separate line for Install SQL Server Management Tools in the Installation tab. This separate line forwards you to the Microsoft SQL Server Download Center from where you can download a separate installation file to install the SQL Server Management Studio. This installation file includes all SQL Server Management Tools including SQL Server Management Studio, SQL Server command-line utilities, SQL Server PowerShell provider, SQL Server Profiler and the Database Tuning Advisor, as shown below:

与SQL Server 2016相似，SQL Server 2017共享功能安装媒体中不包含SQL Server Management Studio安装。取而代之的是，在“ 安装”选项卡中会为“ 安装SQL Server管理工具”找到单独的一行。此单独的行将您转到Microsoft SQL Server下载中心，从此处可以下载单独的安装文件以安装SQL Server Management Studio。该安装文件包括所有SQL Server管理工具，包括SQL Server Management Studio，SQL Server命令行实用程序，SQL Server PowerShell提供程序，SQL Server Profiler和数据库优化顾问，如下所示：

Once the installation file is downloaded completely, you can easily go through the simple installation steps for the SQL Server Management Studio that starts with the window shown below:

完全下载安装文件后，您可以轻松完成SQL Server Management Studio的简单安装步骤，该步骤从以下所示的窗口开始：

If there is another version of SQL Server Management Studio installed previously in your machine, SSMS 17.2 will not upgrade or replace the already exists copy, it will be installed side by side with that old version, with the ability to work on both copies on that machine, by choosing between it as shown below:

如果您的计算机上先前安装了另一个版本SQL Server Management Studio，SSMS 17.2将不会升级或替换已经存在的副本，它将与该旧版本并排安装，并且能够在该版本上同时使用两个副本。机器之间进行选择，如下所示：

In this article, we will go through the new enhancements that are included in the SQL Server Management Studio 17.2 version.

在本文中，我们将介绍SQL Server Management Studio 17.2版中包含的新增强功能。

连接对话框 (The Connection Dialog Box)

In SQL Server Management Studio 17.2, the connection dialog box supports an extra three authentication methods that are used to connect to Azure SQL Server Database and Data Warehouse instances, in addition to the old Windows and SQL Server authentication methods. The five supported authentication methods are listed below:

在SQL Server Management Studio 17.2中，连接对话框除了旧的Windows和SQL Server身份验证方法外，还支持用于连接到Azure SQL Server数据库和数据仓库实例的其他三种身份验证方法。下面列出了五种支持的身份验证方法：

Windows Authentication
Windows验证
SQL Server Authentication
SQL Server身份验证
Active Directory – Universal with MFA support Active Directory –具有MFA支持的通用目录
Active Directory – Password
Active Directory –密码
Active Directory – Integrated
Active Directory –集成

The Active Directory – Password and Active Directory – Integrated are non-interactive authentication methods supported by the Active Directory Universal Authentication and can be used in many applications such as the ODBC and JDBC. The Active Directory – Universal with MFA support is an interactive authentication method that supports Azure Multi-Factor Authentication, which provides a strong authentication with a range of easy verification options, and the user can choose the method that he prefers.

集成的Active Directory（密码）和Active Directory（Active Directory）是Active Directory通用身份验证支持的非交互式身份验证方法，可以在许多应用程序中使用，例如ODBC和JDBC。具有MFA支持的Active Directory – Universal是一种交互式身份验证方法，它支持Azure多重身份验证，该身份验证提供了强大的身份验证以及一系列简单的验证选项，用户可以选择自己喜欢的方法。

The five authentication methods are shown in the Authentication drop down list below:

以下“身份验证”下拉列表中显示了五种身份验证方法：

默认脚本选项 (The Default Scripting Option)

In the previous SQL Server Management Studio versions, the generated script will target the latest released SQL Server version. In SQL Server Management Studio 17.2, the Match Script Settings to Source has been added, with the default True value means that the generated script will target the source SQL Server instance’s version, edition, and engine type, where the False value will force the scripting to behave as the previous SQL Server Management Studio versions.

在以前SQL Server Management Studio版本中，生成的脚本将针对最新发布SQL Server版本。在SQL Server Management Studio 17.2中，添加了“与源代码匹配脚本设置” ，默认值为True表示生成的脚本将针对源SQL Server实例的版本，版本和引擎类型，其中False值将强制脚本编写表现为以前SQL Server Management Studio版本。

The Match Script Settings to Source option in addition to all scripting options are no longer available under the General Scripting Options section. They can be found now under its own new section, called the Version Options. You can browse it by choosing Options from Tools in the menu bar as shown below:

除了“所有脚本”选项之外，“通用脚本选项”部分下的“将脚本设置匹配到源”选项不再可用。现在可以在其自己的新部分（称为版本选项）下找到它们。您可以通过从菜单栏中的“ 工具”中选择“ 选项”来浏览它，如下所示：

Under the SQL Server Object Explorer tab, choose the Scripting sub-tab and you can override the True default value of the Match Script Settings to Source option under Version Options section as you can see below:

在“ SQL Server对象资源管理器”选项卡下，选择“ 脚本”子选项卡，然后您可以覆盖“版本选项”部分下“将脚本设置匹配到源”选项的True默认值，如下所示：

嵌入式性能仪表板 (The Embedded Performance Dashboard)

Microsoft SQL Server 2005 Performance Dashboard provides us with a group of reports that help database administrators in identifying different types of performance issues and resolve it easily. These performance reports present the performance statistics using the system Dynamic Management Views. With all the updates applied to the performance dashboard to work with the new SQL Server versions and take benefits from the newly defined dynamic management views, it still not included within the SQL Server installation media, and requires a separate download for its installation file from the Microsoft download page to be installed on your machine.

Microsoft SQL Server 2005性能仪表板向我们提供了一组报告，可帮助数据库管理员确定不同类型的性能问题并轻松解决。这些性能报告使用系统动态管理视图显示性能统计信息。由于所有更新已应用到性能仪表板上以与新SQL Server版本一起使用并从新定义的动态管理视图中受益，因此它仍未包含在SQL Server安装介质中，并且需要从以下位置单独下载其安装文件：要在您的计算机上安装的Microsoft下载页面。

The good news is that, in SQL Server Management Studio 17.2, the Performance Dashboard is included as a built-in Standard Report, and available for use without any extra download or installation process. You need only to connect your SQL Server 2008 or later instance using the SQL Server Management Studio 17.2 and open the Performance Dashboard to browse its different performance reports. From the SQL Server Management Studio, right-click on the instance name, choose Reports, then Standard Reports, from where you can open the Performance Dashboard embedded reports as shown below:

好消息是，在SQL Server Management Studio 17.2中，性能仪表板作为内置的标准报告包含在内，无需任何额外的下载或安装过程即可使用。您只需要使用SQL Server Management Studio 17.2连接SQL Server 2008或更高版本的实例，然后打开性能仪表板即可浏览其不同的性能报告。在SQL Server Management Studio中，右键单击实例名称，选择Reports ，然后选择Standard Reports ，从中可以打开Performance Dashboard嵌入式报告，如下所示：

The Performance Dashboard provides us with performance data that can be used to detect and resolve many performance issues, such as the CPU bottlenecks, I/O bottlenecks, missing indexes and blocking issues. The first view of the Performance Dashboard that you will see when you open it from the SSMS 17.2 will be as below:

性能仪表板为我们提供了性能数据，可用于检测和解决许多性能问题，例如CPU瓶颈，I / O瓶颈，索引丢失和阻塞问题。从SSMS 17.2打开性能仪表板时，您将看到它的第一个视图，如下所示：

As you can see from the previous report, the Performance Dashboard provides us with useful information about the most expensive queries in terms of CPU consumption, I/O consumption, execution time, logical and physical reads and logical writes, with the ability to dive deeply in the reports to get detailed information about these queries, as shown below:

从上一份报告中可以看到，性能仪表板向我们提供了有关最昂贵查询的有用信息，包括CPU消耗，I / O消耗，执行时间，逻辑和物理读取以及逻辑写入，以及深入了解的能力。在报告中获取有关这些查询的详细信息，如下所示：

The Performance Dashboard helps us in getting information about missing indexes that could help DBAs improve query performance as shown below:

Performance Dashboard帮助我们获取有关缺失索引的信息，这些信息可以帮助DBA改善查询性能，如下所示：

In addition to metadata about the monitored databases hosted in the connected SQL Server instance as follows:

除了有关连接SQL Server实例中托管的受监视数据库的元数据外，还如下所示：

Showplan节点搜索 (Showplan Node Search)

When you analyze a big execution plan to identify a specific operator, table name, column name or the node with the highest cost, it may take you a long time and big effort to find it, due to a large number of nodes and information provided in the execution plan. It was a dream from the database administrator to click CRTL+F and search for such plan information. With SQL Server Management Studio 17.2 the dream comes true, as it includes the ability to search for a lot of information in the graphical showplan. This new search feature can be used with any feature that uses the graphical showplan, such as the Query Store, the Plan Comparison, and the Plan Scenarios.

当您分析大型执行计划以识别特定的运算符，表名，列名或成本最高的节点时，由于提供了大量的节点和信息，可能会花费很长时间并花费大量精力来查找它在执行计划中。数据库管理员梦想着单击CRTL + F并搜索此类计划信息。使用SQL Server Management Studio 17.2，梦想成真了，因为它具有在图形展示计划中搜索大量信息的能力。这项新的搜索功能可与任何使用图形显示计划的功能一起使用，例如查询存储，计划比较和计划方案。

To start the node information search in the opened graphical showplan, right-click on the plan and choose Find Node as shown below:

要在打开的图形显示计划中开始节点信息搜索，请右键单击该计划，然后选择“ 查找节点” ，如下所示：

Or just click CTRL+F when you open the graphical showplan, and a new search bar will be displayed. Assume that we need to tune the below SELECT query by studying the graphical execution plan using SQL Server Management Studio 17.2:

或在打开图形显示计划时单击CTRL + F ，将显示一个新的搜索栏。假设我们需要通过使用SQL Server Management Studio 17.2研究图形执行计划来调整以下SELECT查询：

 
SELECT Emp_First_Name , Emp_Last_Name , Emp_Adress , EMP_PhoneNumber , EMP_Salary , DEP.DEP_Name ,DEP.DEP_Location  FROM Employees EMP
JOIN Departments DEP
ON EMP.EmpDepID =DEP.DEP_ID
WHERE Emp_Adress like '%USA%' OR Emp_Last_Name LIKE '%EVA%'

Once the graphical plan is opened, click CTRL+F and the search bar will be displayed where you can start the node information search on the opened plan as shown below:

打开图形计划后，单击CTRL + F，将显示搜索栏，您可以在其中开始对打开的计划进行节点信息搜索，如下所示：

The new search mechanism allows you to choose from different properties, making the graphical showplan navigation process easy. With the ability to search for an exact value using (=) operator or a similar value using the (Contains) operator. The list of properties that you can search for are shown below:

新的搜索机制使您可以从不同的属性中进行选择，从而简化了图形显示计划导航过程。可以使用（ = ）运算符搜索精确值，或者使用（ Contains ）运算符搜索相似值。您可以搜索的属性列表如下所示：

For example, you can search for a node that represents the operation on a specific table, by choosing the table property, choose the comparison operator, which is contains in my example, then write the table name in the blank and click on the arrow that will show you the search result as shown below:

例如，您可以搜索代表特定表上的操作的节点，方法是选择表属性，然后选择比较运算符，该运算符包含在我的示例中，然后将表名写在空白处并单击箭头，将显示搜索结果，如下所示：

Another example, is to search for the node that reads more than 1000 rows, by choosing the ActualRows property, choose the comparison operator, which is contains in the below example, then write (1000) in the blank and click on the arrow in order to see the search result as shown below:

另一个示例是，通过选择ActualRows属性来搜索读取多于1000行的节点，选择比较运算符，该运算符包含在下面的示例中，然后在空格中写入（1000），然后依次单击箭头查看搜索结果，如下所示：

The last example here, with infinite number of search criteria that you can perform, is to search for the node with a specific operator, which is scan operator in our situation, by choosing the PhysicalOp property, choose the comparison operator, which is contains in the below example, then write (scan) in the blank and click on the arrow and you will see the search result as shown below:

您可以执行的搜索条件数量不限，这里的最后一个示例是使用特定运算符（在我们的情况下为扫描运算符）搜索节点，方法是选择PhysicalOp属性，然后选择比较运算符，该运算符包含在下面的示例，然后在空白处写（扫描）并单击箭头，您将看到如下所示的搜索结果：

结论 (Conclusion)

SQL Server Management Studio is a graphical user interface that can be used to develop and administer the SQL Server instances hosted on the local machine, remote server or in the cloud. SQL Server Management Studio 17.2 is the latest released version of the SSMS that comes with a number of enhancements such as the new authentication method used to connect to Azure SQL Database and SQL Data Warehouse, the new scripting options, the integrated Performance Dashboard and the Showplan node search that are described in details within this article.

SQL Server Management Studio是一个图形用户界面，可用于开发和管理本地计算机，远程服务器或云中托管SQL Server实例。 SQL Server Management Studio 17.2是SSMS的最新发行版，具有许多增强功能，例如用于连接到Azure SQL数据库和SQL数据仓库的新身份验证方法，新脚本选项，集成的性能仪表板和Showplan。本文中详细介绍的节点搜索。

Next articles in this series

本系列的下一篇文章