开发人员SQL Server代码检查清单

In a software development life cycle, Code Review plays an integral role in improving the product quality. Having a Code Review Checklist is indispensable since it ensures that the best practices are followed and reviews are performed consistently. It is essential for developers to be aware of the coding guidelines while working on their code changes. Catching a bug early in the process is inexpensive and easier to resolve, than compared with a bug caught later in the game. Having all the common mistakes added to the checklist document is a great way to create awareness and ensure good code quality over a period of time.

在软件开发生命周期中，Code Review在提高产品质量方面起着不可或缺的作用。 拥有代码检查清单是必不可少的，因为它可以确保遵循最佳实践并始终如一地进行检查。 对于开发人员来说，在进行代码更改时必须了解编码准则，这一点至关重要。 与游戏后期发现的错误相比，在过程中尽早发现错误的成本低且易于解决。 将所有常见错误添加到检查清单文档中是在一段时间内提高知名度并确保良好代码质量的好方法。

In this article, we will look at a few of the SQL Server Code Review Guidelines which you can incorporate into your day to day database development work. A code review checklist can sometimes become pretty overwhelming, hence I have tried to mention 10 important guidelines which you can adhere to.

在本文中，我们将研究一些SQL Server代码审查指南，您可以将它们纳入日常数据库开发工作中。 代码复查清单有时有时会变得不堪重负，因此，我试图提及您可以遵循的10条重要准则。

确认您选择了最有效的数据类型 (Verify that you have selected the most efficient data type)

It is essential that you choose the best data type to store your data, which aligns with your business requirements. An improper selection of datatype leads to bad database design and can potentially result in performance issues. SQL Server provides a wide range of data types which you can choose from. You must ensure that you allocate the right amount of disk space for your data. Remember that the bigger the data type you use, the more storage it takes.

必须选择最佳的数据类型来存储数据，这符合业务需求。 数据类型选择不当会导致数据库设计不良，并可能导致性能问题。 SQL Server提供了多种数据类型供您选择。 您必须确保为数据分配正确的磁盘空间量。 请记住，使用的数据类型越大，占用的存储空间越多。

One great tip while selecting your data types is to be aware of the fixed data type (CHAR) vs dynamic one (VARCHAR). Let me explain it with a simple example –

选择数据类型时的一个重要提示是要了解固定数据类型（CHAR）与动态数据类型（VARCHAR）。 让我用一个简单的例子来解释它–

CHAR(20) uses 20 characters for storing, irrespective of the fact that you actually have to store a value of less than 20 characters.

CHAR （20）使用20个字符进行存储，而实际上您实际上必须存储的值小于20个字符。

VARCHAR(20) is flexible and uses only the storage that is required. Hence if you have strings which are varying in size, it is recommended to use variable datatype, and in the process can save a lot of storage space.

VARCHAR （20）是灵活的，仅使用所需的存储。 因此，如果您使用的字符串大小不同，建议使用可变数据类型，这样可以节省大量存储空间。

Doing your due diligence while selecting your data types can save you from lot of rework and performance bottlenecks at later stages of your application development. I would highly recommend you to carefully review the data types for your database objects.

在选择数据类型时进行尽职调查可以在应用程序开发的后期避免繁琐的工作和性能瓶颈。 我强烈建议您仔细检查数据库对象的数据类型。

验证工作数据集的大小是否最小 (Verify that the working data set is minimal in size)

Looking at your Business requirements, always try to figure out the data you want to fetch from the database. There is no need to extract additional information from the database and pass it back to the client application, which will not be used. Not only does it create more overhead and resource wastage, it also considerably slows down the speed.

在查看业务需求时，请始终尝试找出要从数据库中获取的数据。 无需从数据库中提取其他信息并将其传递回客户端应用程序，该信息将不会被使用。 它不仅造成更多的开销和资源浪费，而且还大大降低了速度。

Always try to avoid SELECT * operation and use explicit column list as far as possible.

始终尝试避免执行SELECT *操作，并尽可能使用显式列列表。

Not only the columns, you should also try to have less rows in your working result set by adding filter criteria. A SELECT statement without a WHERE clause should be avoided, since it would lead to a bigger result set – causing table scan operations and hence leading to performance issues. Once you have narrowed down your data columns, make sure to use a WHERE clause and have additional search conditions to further reduce your result set size.

通过添加过滤条件，不仅列，还应尝试在工作结果集中减少行数。 应该避免使用没有WHERE子句的SELECT语句，因为它会导致更大的结果集–导致表扫描操作，从而导致性能问题。 缩小数据列的范围后，请确保使用WHERE子句并具有其他搜索条件，以进一步减小结果集的大小。

Looking at the below two SQL queries, it is advisable to use Query 2 over Query 1 –

查看以下两个SQL查询，建议使用查询2而不是查询1 –

 
SELECT *
FROM [AdventureWorks2008].[HumanResources].[Employee]
 
SELECT LoginID, JobTitle
FROM [AdventureWorks2008].[HumanResources].[Employee]
WHERE OrganizationLevel = 2
 

验证命名约定是否明确且有意义 (Verify that the naming conventions are clear and meaningful)

To create a maintainable application, you need to make your code tell its own story. This can be easily achieved by giving understandable and meaningful names to your variables, tables, views, functions, stored procedures and other database objects. This helps in making the code self-explanatory and minimizes the need for explicit commenting.

要创建可维护的应用程序，您需要使代码讲述自己的故事。 通过为变量，表，视图，函数，存储过程和其他数据库对象赋予易于理解且有意义的名称，可以轻松实现此目的。 这有助于使代码易于说明，并最大程度地减少了显式注释的需要。

From an application readability and maintenance perspective, comments are indispensable. It is always a good practice to add concise comments, when you are working on a complex Business functionality. This will help another developer to understand why the change was made. But make sure not to over comment – let the code tell its own story. If you are unit testing your stored procedures, then let the Unit tests do the talking. Unit Tests, unlike comments, can never be outdated – they will give you an immediate feedback when the functionality breaks/changes.

从应用程序的可读性和维护性的角度来看，注释是必不可少的。 在使用复杂的业务功能时，最好添加简洁的注释。 这将有助于其他开发人员了解进行更改的原因。 但是请确保不要过度注释–让代码讲述自己的故事。 如果要对存储过程进行单元测试，请让单元测试进行讨论。 与注释不同，单元测试永远不会过时-当功能中断/更改时，它们将立即为您提供反馈。

I would highly recommend to verify the comments and naming conventions during the review process. Business Requirements changes often, and so does the code. Hence it is essential that the associated comments are updated as well – because if the comments and code are not in sync with each other, it can cause lot of confusion and increase your technical debt over a period of time.

我强烈建议您在审核过程中验证评论和命名约定。 业务需求经常更改，代码也经常更改。 因此，必须同时更新相关的注释-因为如果注释和代码彼此之间不同步，则可能会引起很多混乱，并在一段时间内增加您的技术负担。

验证您SQL Server数据的安全性 (Verify the security of your SQL Server data)

Data Security is a critical issue and we should always ensure that our data is protected. Generally we store sensitive information like personal phone number, email, bank account details, and credit card information in our database tables. From a security perspective, we need to make sure that the personally-identifiable information(PII) are secured from unauthorized access, either by encryption or implementing customizable masking logic.

数据安全是一个关键问题，我们应该始终确保数据受到保护。 通常，我们在数据库表中存储敏感信息，例如个人电话号码，电子邮件，银行帐户详细信息和信用卡信息。 从安全角度来看，我们需要确保通过加密或实现可定制的屏蔽逻辑来保护个人身份信息（PII），以防止未经授权的访问。

While reviewing the SQL code, try to check for possible scenarios which are susceptible to SQL Injection
attack – a security vulnerability issue where malicious code in inserted into strings and passed to SQL server instance for execution.

在查看SQL代码时，请尝试检查是否可能受到SQL注入的影响
攻击 –一个安全漏洞问题，其中恶意代码被插入字符串中并传递给SQL Server实例以执行。

If you dynamically build your SQL queries by concatenating strings and not use parameterized queries or stored procedures, you are susceptible to SQL Injection attack. Hence I would suggest you to review your ad hoc SQL queries, and evaluate if it can be converted into a stored procedure.

如果通过串联字符串动态构建SQL查询，而不使用参数化查询或存储过程，则容易受到SQL Injection攻击。 因此，我建议您检查临时SQL查询，并评估它是否可以转换为存储过程。

验证您的应用程序具有良好的资源管理 (Verify that your application has good Resource Management)

Lot of client applications use ADO.NET to connect to the database, execute commands and retrieve data. By using Data Provider for SQL Server, you will be able to open and close connections, fetch data and update them. Two important sections of a data provider are:

许多客户端应用程序使用ADO.NET连接到数据库，执行命令和检索数据。 通过使用SQL Server的数据提供程序，您将能够打开和关闭连接，获取数据并更新它们。 数据提供者的两个重要部分是：

• SQLConnection object – used to establish a connection with the SQL Server database SQLConnection对象–用于与SQL Server数据库建立连接
• SQLCommand object – used to prepare a stored procedure or ad-hoc SQL statement that you want to execute on a database SQLCommand对象–用于准备要在数据库上执行的存储过程或临时SQL语句

Once you are done with executing commands and retrieving data, you need to make sure to release all the associated objects. In C#, you can utilize the USING block to timely close/release your connection and command objects. I would recommend to verify this during the review process, so that you can prohibit any memory leaks and performance issues in your application.

完成执行命令和检索数据后，您需要确保释放所有关联的对象。 在C＃中，可以利用USING块及时关闭/释放连接和命令对象。 我建议在检查过程中对此进行验证，以便可以禁止应用程序中的任何内存泄漏和性能问题。

Same applies to your database objects. It is always a good practice to cleanup resources – drop your temporary tables, close and deallocate cursors – which will ensure that you are releasing the current data and associated locks.

同样适用于您的数据库对象。 清理资源始终是一个好习惯-删除临时表，关闭并释放游标-这将确保您释放当前数据和关联的锁。

通过临时SQL使用存储过程可提高性能和可维护性 (Use stored procedures over ad hoc SQL for better Performance and Maintainability)

There are multiple benefits of using stored procedures in your database development work. Stored procedures helps in caching the execution plans and reusing it. Even ad hoc queries can reuse the execution plan, but a slight change in parameter values will force the Optimizer to generate a new execution plan.

在数据库开发工作中使用存储过程有很多好处。 存储过程有助于缓存执行计划并重新使用它。 即使是临时查询也可以重用执行计划，但是参数值的微小变化将迫使优化器生成新的执行计划。

Also using stored procedures will reduce network traffic and latency, hence improving application performance. Over the network, what gets passed from the client application to the database server is something like –
EXEC [dbo].[sp_Get_Next_Order_Number]

另外，使用存储过程将减少网络流量和延迟，从而提高应用程序性能。 通过网络，从客户端应用程序传递到数据库服务器的内容类似于–
EXEC [dbo]。[sp_Get_Next_Order_Number]

Now compare that to passing a complete ad hoc query over the network and the additional overhead it will create!

现在，将其与通过网络传递完整的即席查询及其带来的额外开销进行比较！

With a Stored Procedure, Maintainability is easier since you have a single place to modify code, if requirement changes. Stored procedure encapsulates logic, hence the code can be changed centrally and reused, without affecting the various clients using it. This won’t be possible if you have ad hoc queries written throughout your application.

使用存储过程，可维护性变得更容易，因为如果需求发生变化，您可以在一个地方修改代码。 存储过程封装了逻辑，因此可以对代码进行集中更改和重用，而不会影响使用它的各种客户端。 如果您在整个应用程序中都编写了临时查询，那么这将是不可能的。

注意不推荐使用的功能，并确保不要在任何新代码中使用它们 (Be aware of deprecated features and make sure not to use them in any of your new code)

With every release of a SQL Server version, Microsoft announces the ‘Backward Compatibility’ with prior versions. This is typically categorized into 4 primary categories — Deprecated, Discontinued, Breaking and Behavior changes. If a feature is listed as Deprecated, then it indicates that Microsoft is stating its disapproval in the usage of that feature in any future development work.

在SQL Server版本的每个发行版中，Microsoft都宣布与以前的版本具有“ 向后兼容性 ”。 通常将其分为4个主要类别-已弃用，已停产，破坏和行为更改。 如果某个功能被列为“已弃用”，则表明Microsoft在以后的任何开发工作中均表示不赞成使用该功能。

You can view all the deprecated features in SQL Server 2016 here. These features are scheduled to be removed in a future version of SQL Server and hence during the review process make sure that you are not using it in new application development work.

您可以在此处查看SQL Server 2016中所有不推荐使用的功能。 这些功能计划在SQL Server的将来版本中删除，因此在审阅过程中，请确保您没有在新的应用程序开发工作中使用它。

One such deprecated feature which lot of legacy applications still use are the data types NTEXT, TEXT and IMAGE. You should avoid using these data types in new development activities and also plan to modify applications that currently use them. Instead, you can use NVARCHAR() , VARCHAR() and VARBINARY().

许多遗留应用程序仍在使用的一种不赞成使用的功能是数据类型NTEXT ， TEXT和IMAGE 。 您应该避免在新的开发活动中使用这些数据类型，并计划修改当前使用它们的应用程序。 而是可以使用NVARCHAR（），VARCHAR（）和VARBINARY（） 。

Another deprecated syntax is the use of old-style join syntax –

另一种不赞成使用的语法是使用老式的联接语法–

 
select a.id, substring(b.name,1,40) as 'table name', count(colid) as 'count' 
from syscolumns a, sysobjects b
where a.id = b.id
group by a.id, b.name
 

The usage of explicit JOIN syntax is recommended in all cases –

建议在所有情况下都使用显式JOIN语法–

 
select a.id, substring(b.name,1,40) as 'table name', count(colid) as 'count'
from syscolumns a
inner join sysobjects b
on a.id = b.id
group by a.id, b.name
 

实施结构化错误处理以捕获运行时错误 (Implement structured error handling to capture runtime errors)

Error handling in your SQL code is critical. Sometimes you might need to inform the caller application if there is an issue and in other cases you might need to handle the error at your end and not throw an error back to the client application. In the first scenario, you can use a THROW statement with an error number. Whereas in the second scenario, you can use the TRY..CATCH construct.

SQL代码中的错误处理至关重要。 有时您可能需要在出现问题时通知调用者应用程序，而在其他情况下，则可能需要在最后处理错误，而不是将错误返回给客户端应用程序。 在第一种情况下，可以使用带有错误号的THROW语句。 在第二种情况下，您可以使用TRY..CATCH构造。

I would suggest to leverage the TRY…CATCH construct in your code to prevent the application from throwing a runtime error. It is as simple as putting all your SQL code inside the TRY block – and then when an error occurs the control is automatically transferred to the CATCH block, where you have the authority to decide how to handle the error. Another point worth mentioning here is that inside your CATCH block, you will have access to additional error functions like ERROR_NUMBER, ERROR_LINE, ERROR_MESSAGE, ERROR_SEVERITY, ERROR_PROCEDURE and ERROR_STATE.

我建议在代码中利用TRY…CATCH构造，以防止应用程序引发运行时错误。 就像将所有SQL代码放入TRY块一样简单，然后在发生错误时将控件自动转移到CATCH块，在这里您有权决定如何处理错误。 在这里值得一提的另一点是，您可以在CATCH块中访问其他错误函数，例如ERROR_NUMBER，ERROR_LINE，ERROR_MESSAGE，ERROR_SEVERITY，ERROR_PROCEDURE和ERROR_STATE 。

Find below a simple demonstration –

在下面找到一个简单的演示–

 
BEGIN TRY
    SELECT  9 / 0; -- Divide by Zero
END TRY
BEGIN CATCH
    SELECT  ERROR_LINE() AS 'Line' ,
            ERROR_MESSAGE() AS 'Message' ,
            ERROR_NUMBER() AS 'Number' ,
            ERROR_SEVERITY() AS 'Severity' ,
            ERROR_STATE() AS 'State';
END CATCH;
 

验证索引策略以确保高性能 (Verify your Indexing strategy to ensure high performance)

As you implement new Business requirements and write more TSQL code, it is imperative that you will need to create new indexes.

当您实现新的业务需求并编写更多的TSQL代码时，必须创建新的索引。

Indexing is generally the easiest way to improve the performance of your SQL Server queries. However if not designed right, it might hurt your application performance negatively as well. For instance, if you have an OLTP system, then you should review all the newly created indexes carefully to determine if they are really needed. Limiting the number of indexes is important as well, else it will slow down the Data Manipulation operations like INSERT/DELETE/UPDATE. Not to forget the additional disk space the indexes consume.

索引通常是提高SQL Server查询性能的最简单方法。 但是，如果设计不当，也会对应用程序性能造成负面影响。 例如，如果您有OLTP系统，则应仔细检查所有新创建的索引，以确定是否确实需要它们。 限制索引的数量也很重要，否则会减慢诸如INSERT / DELETE / UPDATE之类的数据处理操作。 不要忘记索引占用的额外磁盘空间。

You can also think of using an automated tool like Database Tuning Advisor for your application in a Test Environment to review the indexing suggestions. Based on your analysis of the recommendations, you can create the necessary indexes. Do not blindly create all the indexes which the tool recommends.

您还可以考虑在测试环境中为您的应用程序使用诸如Database Tuning Advisor之类的自动化工具来查看索引建议。 根据对建议的分析，可以创建必要的索引。 不要盲目创建该工具推荐的所有索引。

One final thing which you should review after creating a new index is whether the index actually served its purpose. You can generate the execution plan of the query and quickly verify if the table scan/index scan operation is now replaced with an index seek operation.

创建新索引后，您需要检查的最后一件事是该索引是否真正达到了其目的。 您可以生成查询的执行计划，并快速验证是否已将表扫描/索引扫描操作替换为索引查找操作。

性能优化是一项功能，需要不断进行监控和审查 (Performance Optimization is a feature and needs to be continuously monitored and reviewed )

One last thing which constitutes the crux of code review is Performance Optimization. You should be looking at the SQL queries and stored procedures written and the time they take to execute. Keep in mind that your data will grow over the period of time, so a query running fast currently does not indicate that it is optimal.

构成代码审查关键的最后一件事是性能优化 。 您应该查看SQL查询和编写的存储过程，以及执行它们所花费的时间。 请记住，您的数据会随着时间的推移而增长，因此当前运行快速的查询并不表示它是最佳的。

For medium or large sized queries, you should generate the execution plan and look out for expensive operators, table scans, index scans, key lookups, missing indexes and more. These are good indicators as to what you need to tune for boosting your database performance.

对于中型或大型查询，您应该生成执行计划，并注意昂贵的运算符，表扫描，索引扫描，键查找，缺少索引等。 这些是指示您需要进行哪些调整以提高数据库性能的良好指标。

Looking at the execution plan extract above, we can easily see the Parallelism operator consuming the highest cost. As you can notice, each operator’s contribution to the overall query cost is displayed as a percentage value. Also the arrow width between operators is an indication of the number of rows affected by an operator. Generally to improve query performance, you can add a filter condition to your query to reduce the number of rows. Once done, this will reflect in the execution plan with a decreased arrow width.

查看上面的执行计划摘要，我们可以轻松地看到Parallelism运算符消耗的成本最高。 您会注意到，每个操作员对总查询成本的贡献都显示为百分比值。 同样，运算符之间的箭头宽度也表示受运算符影响的行数。 通常，为了提高查询性能，可以在查询中添加过滤条件以减少行数。 完成后，这将以减小的箭头宽度反映在执行计划中。

结论 (Conclusion)

To summarize, having a Code Review process in place has multiple benefits like consistent application design, identification of issues early in the process, awareness of best practices among team members and improvement of the overall code quality. If you do not have a Code Review checklist available, you can use this as a baseline document, and add to it based on your specific environment. If you already have a code review checklist with you, do let me know in the comments below few of the other guidelines which you follow.

总而言之，实施代码审查流程具有多个好处，例如一致的应用程序设计，在流程早期发现问题，了解团队成员的最佳实践以及提高整体代码质量。 如果没有可用的“代码审查”清单，则可以将其用作基准文档，并根据您的特定环境添加到该文档中。 如果您已经有了代码检查清单，请在下面的评论中让我知道您要遵循的其他一些指南。

翻译自: https://www.sqlshack.com/sql-server-code-review-checklist-for-developers/