在SQL Server Management Studio中提高编码速度

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.

无论是T-SQL开发人员还是DBA，我们都会花费大部分时间在SQL Server Management Studio中工作。 在本文中，我们将学习许多技巧和窍门，这些技巧和窍门将提高我们在SSMS IDE中的生产率和效率。 我们将探索停止手动重复任务，使用快捷方式，减少键入并在最短时间内生成更多代码行的方法。

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

在我的上一篇文章中，我们研究了SSMS 2016的新增功能和改进功能。如果您还没有看过它，我强烈建议您阅读它，安装SQL Server Management Studio 2016并开始利用生产力增强功能– SQL Server Management Studio 2016生产力增强

输入更少并生成更多代码 (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.

SSMS提供了一个很酷的功能，称为“ 代码片段 ”，可以帮助您大大提高编码速度。 代码段是现成的代码段，您可以将其插入T-SQL代码中。 这是节省大量时间的方法，因为在创建存储过程，表，索引等时无需记住任何语法。 您还可以创建自己的自定义代码段，并将其添加到SQL Server中可用的预定义代码段中。

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

在SSMS IDE中，只需单击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.

根据您的选择，您现在将看到用于创建新的非聚集索引的代码段。

 
-- 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.

您还可以按CTRL K + B打开代码段管理器，您可以在其中查看所有代码段并进行管理。

在不执行T-SQL脚本的情况下获取表的行数 (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.

通常，要查找表中的记录数，我们执行T-SQL查询并获取信息。

 
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.

但是SSMS提供了一种即用型功能，该功能可以显示数据库中所有表的行数-无需编写任何显式查询。 “ 对象资源管理器详细信息”窗口是一项功能，很少有开发人员在日常工作中使用。 这提供了一个用户界面，该界面列出了服务器中的所有对象以及其他信息，例如行数，使用的数据空间，使用的索引空间等。

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.

默认情况下，对象资源管理器详细信息在SSMS中不可见。 您可以单击F7或仅导航至“ 视图”->“对象资源管理器详细信息”以查看此窗口。

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‘.

当您在项目中工作时，往往会有许多服务器需要不断切换-本地/ DEV / QA / MOCK / PROD。 保存这些经常访问的服务器的最简单方法是使用称为“注册服务器 ”的功能存储服务器的连接信息。

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’.

要在SSMS中查看此窗口，可以导航至查看->已注册服务器。 然后，右键单击“本地服务器组”，然后单击“新服务器注册”。

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.

我已经在我的项目中的“本地服务器组”文件​​夹下注册了所有常用服务器，如下面的屏幕快照所示。

同时对多个服务器执行SQL查询 (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.

使用“注册服务器”功能注册所有常用服务器后，所有服务器将显示在“本地服务器组”下。 您也可以创建自己的组并在其下选择服务器。 创建此类组的一大优势是，它使您能够对该组中的所有数据库服务器执行单个TSQL查询。

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.

在查询编辑器中，您可以键入TSQL查询并运行它。 结果窗格将显示针对该组中所有服务器触发的查询结果。

 
-- Running a custom script against all my database servers
SELECT COUNT(1) AS 'Statistics RowCount'
FROM [perf].[Statistics]  
 

利用预定义SQL脚本节省时间 (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!

在编写T-SQL查询时，如果您不记得语法，则会花时间搜索在线文章或参考书以找出语法并制定查询。 Google永远不会让我们失败！

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.

大多数开发人员都不知道SSMS中称为“ 模板浏览器 ”的内置功能-它在其中开箱即用地显示了许多模板。 这些模板包含占位符/参数，这些占位符/参数可帮助您立即创建许多数据库对象，例如表，索引，视图，函数，存储过程等等。 模板随每个SQL Server版本进行更新，因此您可以放心，您正在使用最新语法。

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.

现在，如果单击“创建数据库”，它将在查询窗口中打开相应的模板。

 
-- =============================================
-- 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.

您可以通过按CTRL + SHIFT + M替换参数，这会弹出一个新窗口，您可以在其中指定参数值。

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

用有效的名称替换参数后，将准备好执行最终脚本。

 
-- =============================================
-- 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.

具有多个屏幕可以大大提高生产率，因为您不再需要在多个程序之间不断地来回切换。 您可以利用并排比较的优势，还可以轻松地在屏幕之间共享数据。 话虽如此，在SSMS中，您可以使用“ 水平或垂直选项卡组 ”，通过使用单独的代码窗口来最大化您的工作空间，同时利用拥有多个屏幕的相同好处。

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.

打开多个选项卡后，可以右键单击任何选项卡，然后根据自己的喜好在“水平”选项卡与“垂直”选项卡组之间进行选择。

 
-- 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.

每个代码窗口都可以建立与SQL Server实例的独立连接，因此您可以对不同的服务器实例执行相同的查询并比较结果。 您还可以并排比较脚本文件或其结果。

使用键盘快捷键来提高效率 (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.

通常，在使用SSMS时，我们会打开多个查询窗口。 通过按CTRL + TAB，将打开一个对话框，其中包含所有打开的选项卡，您可以非常方便地在它们之间进行切换。 这是我每天使用的一种快捷方式。

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

我在下面列出了一些我最喜欢的SSMS快捷方式，您可以使用它们来提高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

捷径	行动
CTRL + R	在显示和隐藏结果窗格之间切换
CTRL + N	使用当前数据库连接打开新的查询选项卡
CTRL + L	显示查询的估计执行计划
SHIFT + ALT + ENTER	在全屏显示的代码编辑器之间切换
CTRL +]	导航到匹配的括号
CTRL + K + X	插入SQL代码段
Ctrl + Shift + U	将文字更改为大写
Ctrl + Shift + L	将文字更改为小写
CTRL + K + C	评论所选文字
CTRL + K + U	取消注释所选文本

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

要查看SQL Server Management Studio键盘快捷键的完整列表，请参考此MSDN链接– SQL Server Management Studio键盘快捷键。

利用正则表达式更快地查找和替换 (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.

若要启用正则表达式，请单击CTRL + SHIFT + H以打开“查找和替换”对话框。 然后，您可以单击“查找内容”字段旁边的“表达式生成器”图标。 它将显示最常用的表达式，您可以利用它们来查找特定的模式。

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

你可以找到一个关于使用在MSDN链接的正则表达式搜索的详细信息使用正则表达式搜索文本

结论 (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.

对IDE及其功能有最高级的了解，对提高生产力有很长的路要走。 在本文中，我们介绍了许多SSMS技巧和窍门，以提高TSQL开发人员的编码速度。 希望您已经学到了一些有用的技术，可以带回自己并应用于日常工作中。 有关进一步提高编码效率的第三方工具，请参见ApexSQL Complete 。

翻译自: https://www.sqlshack.com/increase-coding-speed-sql-server-management-studio/