@sql 单元测试
This SQL unit testing article is based on the fact that we often learn from the mistakes so the idea is to highlight the mistakes to understand them clearly so that they can be avoided straight away.
此SQL单元测试文章基于这样一个事实，即我们经常从错误中学习，因此其思想是突出显示错误以清楚地理解它们，从而可以立即避免它们。
In this article the ten most common SQL Server unit testing mistakes and their remedies are discussed.
本文讨论了十种最常见SQL Server单元测试错误及其补救措施。
Let us get familiar with the mistakes in SQL unit testing keeping in mind we are not learning the mistakes rather we are learning from the mistakes to improve SQL unit testing skills.
让我们熟悉SQL单元测试中的错误，请记住，我们不是在学习错误，而是从错误中学习以提高SQL单元测试技能。
SQL单元测试错误列表 (SQL Unit Testing Mistakes List)
It is better to list the SQL Server unit testing mistakes first to get an idea of what we are after and then briefly shed some light on the mistakes and their remedies one by one.
最好先列出SQL Server单元测试错误，以了解我们所要解决的问题，然后简要地逐一阐明错误及其补救措施。
Here is the list of the ten most common unit testing mistakes:
以下是十个最常见的单元测试错误列表：
- Writing SQL unit tests without understanding 在不理解的情况下编写SQL单元测试
- No standard naming conventions 没有标准的命名约定
- Writing SQL unit tests to run once 编写SQL单元测试以运行一次
- Relying solely on SQL unit test results 仅依靠SQL单元测试结果
- Depending on actual data 根据实际数据
- No testing pattern in place 没有测试模式
- Repeating setup code in all unit tests 在所有单元测试中重复设置代码
- SQL unit testing with constraints applied 已应用约束SQL单元测试
- Isolating SQL unit tests from database lifecycle management (DLM) 将SQL单元测试与数据库生命周期管理（DLM）隔离
- No clean up strategy in place 没有适当的清理策略
1.编写不理解SQL单元测试 (1. Writing SQL unit tests without understanding)
If you straight away start writing SQL unit test without knowing the concepts of database unit testing then you are making a big mistake.
如果您在不了解数据库单元测试的概念的情况下立即开始编写SQL单元测试，那么您将犯下很大的错误。
A couple of unit tests may survive but ultimately you are going to find yourself in the middle of nowhere because knowledge based understanding of database unit testing is a must in order to write, run and maintain SQL unit tests professionally.
几个单元测试可能会幸免，但是最终您会发现自己无所适从，因为对于专业地编写，运行和维护SQL单元测试，必须具有基于知识的对数据库单元测试的理解。
The minimum requirement is to understand the basic principle of unit testing and SQL Server unit testing framework which is going to be used to implement your unit tests.
最低要求是了解单元测试的基本原理和将用于实现单元测试SQL Server单元测试框架。
SQL单元测试的基本原理 (Basic principle of SQL unit testing )
The basic thing when it comes to unit testing is to understand AAA principle which defines how a database unit test should be written.
涉及单元测试的基本知识是理解AAA原理 ，该原理定义了应如何编写数据库单元测试。
The AAA has the following purpose:
AAA具有以下目的：
This is where you arrange your SQL Server unit testing code such as defining expected results
在这里安排SQL Server单元测试代码，例如定义预期结果
This is where you assemble your code by calling the database object under test and taking down its results.
在这里，您可以通过调用被测数据库对象并记录其结果来汇编代码。
This is the final check which compare your expected results (based on Arrange) with the actual results (based on Assemble) and if all goes well the test has passed else the test fails.
这是最终检查，它将您的预期结果（基于Arrange）与实际结果（基于Assemble）进行比较，如果一切顺利，则测试通过了，否则测试失败。
To get better understanding of AAA principle please refer to article tSQLt – A Forgotten Treasure in Database Unit Testing
为了更好地理解AAA原理，请参阅文章tSQLt –数据库单元测试中被遗忘的宝藏
了解您的单元测试框架 (Knowing your unit testing framework )
If you know the basic principle of SQL unit testing but does know how to implement it in your unit testing framework then this is again a mistake.
如果您了解SQL单元测试的基本原理，但确实知道如何在您的单元测试框架中实现它，那么这又是一个错误。
Getting familiar with your SQL Server unit testing framework is also a must plus you should be able to defend your SQL unit testing framework when challenged.
熟悉SQL Server单元测试框架也是必须的，并且在受到挑战时您应该能够捍卫SQL单元测试框架。
It is important to understand whether the unit testing framework you or your development team has chosen is capable of meeting the requirements or not but apart from this you also need to understand how implement standard unit testing concepts in your chosen unit testing framework.
了解您或您的开发团队选择的单元测试框架是否能够满足要求很重要，但是除此之外，您还需要了解如何在所选的单元测试框架中实现标准单元测试概念。
I would strongly suggest tSQLt, one of the most suitable SQL Server unit testing frameworks written in T-SQL and fully support AAA principle implementation along with advanced database unit testing tasks.
我强烈建议使用tSQLt，这是用T-SQL编写的最合适SQL Server单元测试框架之一，并且完全支持AAA原则的实现以及高级数据库单元测试任务。
Please take a look at tSQLt – A Forgotten Treasure in Database Unit Testing to know more about tSQLt.
请查看tSQLt –数据库单元测试中被遗忘的宝藏，以了解有关tSQLt的更多信息。
2.没有标准的命名约定 (2. No standard naming conventions )
If you are not following standard naming convention in your SQL unit tests then sooner or later you may find yourself in a dark room of ambiguity and may end up spending your time in clarifying things than writing unit tests.
如果您在SQL单元测试中没有遵循标准的命名约定，那么迟早您可能会发现自己处于模棱两可的暗室中，并且最终可能会花费时间来澄清事情而不是编写单元测试。
It is crucial to have some standard naming conventions in place which must be unanimously chosen by your development team.
制定一些标准的命名约定是至关重要的，开发团队必须一致选择这些约定。
There are two things you must take into account regarding naming conventions:
关于命名约定，必须考虑两件事：
- Unit testing framework supporting naming conventions 支持命名约定的单元测试框架
- Naming conventions about naming tests 有关命名测试的命名约定
支持命名约定的单元测试框架 (Unit testing framework supporting naming conventions)
You must wisely choose such a SQL unit testing framework in which naming conventions can be easily followed.
您必须明智地选择这样SQL单元测试框架，在该框架中可以轻松遵循命名约定。
Once you are confident that your chosen SQL Server unit testing framework supports naming conventions then those naming conventions must also be shared with your team and there should be a unanimous decision to what to use.
一旦确定自己选择SQL Server单元测试框架支持命名约定，则这些命名约定也必须与您的团队共享，并且应该决定使用哪种命名约定。
Here again, my vote goes to tSQLt which makes it easy to use and standardise naming conventions.
再次，我的投票投给了tSQLt ，这使它易于使用和标准化命名约定。
TSQLt groups the related unit tests into a single test class which if named sensibly is easy to find, modify and maintain afterwards.
TSQLt将相关的单元测试分组到单个测试类中，如果明智地命名，则以后可以轻松找到，修改和维护。
有关命名测试的命名约定 (Naming conventions about naming tests)
According to Dave Green if you name your test class after the name of your database object under test then this makes unit tests easy to understand and maintain.
根据Dave Green的说法，如果您在测试中的数据库对象名称之后命名测试类，则这将使单元测试易于理解和维护。
For example if your object under test is AddBook stored procedure then it is good to create a test class AddBookTests under which all the unit tests related to this object can be grouped and easily located.
例如，如果您的被测试对象是AddBook存储过程，那么最好创建一个测试类AddBookTests ，与该对象有关的所有单元测试都可以在该类下进行分组并轻松定位。
This scenario can also be mapped to requirement focused objective and in that case think of DailyBookReport object created to meet business specification and then the test class should be named as DailyBookReportTests.
这种情况也可以映射到针对需求的目标，在这种情况下，可以考虑创建满足业务规范的DailyBookReport对象，然后将测试类命名为DailyBookReportTests。
Please remember that these examples are more likely to be applicable to tSQLt unit testing framework because of the generic naming convention support by it.
请记住，这些示例更可能适用于tSQLt单元测试框架，因为它支持通用命名约定。
Please check Conventional SQL Unit Testing with tSQLt in Simple Words to see some more naming convention examples.
请在“简单单词”中检查使用tSQLt的常规SQL单元测试，以查看更多命名约定示例。
3.编写SQL单元测试以运行一次 (3. Writing SQL unit tests to run once)
One of the biggest SQL unit testing mistakes is to write unit tests to run once which means apparently there is nothing wrong with the unit test as long as it has not been run twice or more.
SQL单元测试最大的错误之一是编写一次只能运行一次的单元测试，这意味着只要单元测试没有运行两次或两次以上就显然没有错。
This in itself is a problem because SQL unit tests should not be restricted in terms of how many times they can run as this can create chaos in unit testing.
这本身就是一个问题，因为不应限制SQL单元测试的运行次数，因为这会在单元测试中造成混乱。
You should design your SQL unit test such that it should be consistent even it is run one hundred times.
您应该设计SQL单元测试，以使其即使运行一百次也应保持一致。
In other we should look for the following two things:
换句话说，我们应该寻找以下两件事：
- SQL unit tests must be repeatable SQL单元测试必须是可重复的
- SQL unit testing framework must support repeatable unit tests SQL单元测试框架必须支持可重复的单元测试
You should only write repeatable unit tests and this heavily depends on your choice of SQL Server unit testing framework such as tSQLt which supports repeatable unit tests by design.
您只应编写可重复的单元测试，而这在很大程度上取决于您对SQL Server单元测试框架（例如tSQLt）的选择，该框架通过设计支持可重复的单元测试。
4.仅依靠SQL单元测试结果 (4. Relying solely on SQL unit test results)
A passed unit test does not always mean it is serving the purpose so you should not totally rely on the end result of the SQL unit test.
通过的单元测试并不总是意味着它可以达到目的，因此您不应完全依赖SQL单元测试的最终结果。
Think of a unit test intended to meet some requirement by mistake shows green signal without even knowing what is being checked in it.
想想一个旨在满足某些要求的单元测试会错误地显示绿色信号，甚至不知道检查了什么。
As a database developer you have to be vigilant all the time when creating and running SQL unit tests because it is possible that a test has passed just because it does not do anything so it does not fail.
作为数据库开发人员，您在创建和运行SQL单元测试时必须时刻保持警惕，因为测试可能只是因为它什么都不做而不会失败而通过。
In other words please bear in mind if a SQL unit test has passed then one of the two things have happened:
换句话说，请记住，如果通过了SQL单元测试，则发生了以下两种情况之一：
- Unit test meets the business specification 单元测试符合业务规范
- Unit test has been coded to just pass 单元测试已编码为可以通过
This mind set helps you to investigate issues related to SQL unit tests much earlier.
这种思维方式可以帮助您更早地调查与SQL单元测试有关的问题。
Most of the third party SQL unit testing tools now default there new unit test to fail which is a safer approach, however, there is still a possibility to commit this mistake at later stage of writing unit tests.
现在，大多数第三方SQL单元测试工具都默认有新的单元测试失败，这是一种更安全的方法，但是，仍然有可能在编写单元测试的后期阶段犯此错误。
The database unit tests written in tSQLt give a quick insight to be able to detect whether they are actually meeting a business specification or merely running as place holders.
用tSQLt编写的数据库单元测试提供了快速的洞察力，使其能够检测出它们实际上是在满足业务规范还是仅以占位符的身份运行。
5.根据实际数据 (5. Depending on actual data)
Another SQL unit testing mistake is when your unit test depends on actual data from the database. For example, sometimes in order to make SQL unit tests more and more realistic one might think of using the actual data in the database to be used for SQL unit testing.
另一个SQL单元测试错误是您的单元测试依赖于数据库中的实际数据时。 例如，有时为了使SQL单元测试越来越现实，人们可能会考虑使用数据库中用于SQL单元测试的实际数据。
Please remember according to the database unit testing experts including Dave Green when it comes to unit testing data itself is a dependency.
请记住，根据数据库单元测试专家（包括Dave Green）的说法，涉及单元测试数据本身是依赖项。
For example if you write a SQL unit test against your dev database which must be restricted to specific ten records for this test then to create and run this test is a big mistake because your unit test is depending on a specific set of data in the database.
例如，如果您针对开发数据库编写一个SQL单元测试，该测试必须限于特定的十条记录，则创建和运行此测试是一个大错误，因为您的单元测试取决于数据库中一组特定的数据。
I have seen in the past this happens mostly when you are testing report output procedures to verify the data which is not a good SQL unit testing practice in two ways:
过去，我发现这种情况主要发生在您测试报表输出过程以验证数据时，这是一种不好SQL单元测试实践，它有两种方式：
- Your unit test is not your data verification test 您的单元测试不是您的数据验证测试
- If your unit test depends on the data in the database then it is very limited 如果您的单元测试取决于数据库中的数据，则它非常有限
In other words you should separate your SQL unit tests from data verification tests (their details are beyond the scope of this article) and your unit tests should not rely on actual data in the database rather they should have their own test data closer to the realistic data which is enough for a unit test to comply with standard practices.
换句话说，您应该将SQL单元测试与数据验证测试分开（它们的详细信息不在本文的讨论范围内），并且单元测试不应依赖于数据库中的实际数据，而应拥有更接近实际的测试数据。足以使单元测试符合标准惯例的数据。
Here again tSQLt unit testing framework is a good choice.
在这里，再次使用tSQLt单元测试框架是一个不错的选择。
Please refer to Conventional SQL Unit Testing with tSQLt in Simple Words to see how test data is embedded in unit test which does not need to rely on actual data in the database then.
请参阅“简单词汇”中的使用tSQLt进行的常规SQL单元测试，以了解测试数据如何嵌入到单元测试中，而后者则不必依赖数据库中的实际数据。
6.没有适当的测试模式 (6. No testing pattern in place)
If you are not following any testing pattern then you are committing a mistake because sooner or later this is going to be confusing because of the way testing patterns work.
如果您不遵循任何测试模式，那么您犯了一个错误，因为迟早会由于测试模式的工作方式而造成混乱。
The two most common testing patterns or strategies are as follows:
两种最常见的测试模式或策略如下：
- Conventional SQL unit testing 常规SQL单元测试
- Test-driven database development (TDDD) 测试驱动的数据库开发（TDDD）
At least one of the above testing patterns must be followed and tSQLt supports both despite the fact that it is by design test-driven database development ready.
尽管已通过设计驱动测试的数据库开发准备就绪，但必须至少遵循上述测试模式之一，并且tSQLt支持这两种模式。
You can read more about these testing patterns in the following articles:
您可以在以下文章中阅读有关这些测试模式的更多信息：
- Conventional SQL Unit Testing with tSQLt in Simple Words 简单单词中使用tSQLt进行的常规SQL单元测试
- Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing tSQLt单元测试的测试驱动数据库开发（TDDD）基础
7.在所有单元测试中重复设置代码 (7. Repeating setup code in all unit tests)
Those who heavily write SQL unit tests must know the importance of setup code which is often same but repeated multiple times.
那些大量编写SQL单元测试的人必须知道设置代码的重要性，该代码通常是相同的，但是要重复多次。
The setup code is getting your testing ingredients ready to be used which can be defined once and referenced as many times as possible rather than repeating in every single unit test.
设置代码可以使您的测试成分准备就绪，可以一次定义并引用多次，而不必在每个单元测试中重复进行。
The advanced unit testing frameworks like tSQLt completely supports setup procedure to be defined once and reused as much as possible.
像tSQLt这样的高级单元测试框架完全支持设置过程，只需定义一次即可，并尽可能地重用。
Repeating setup code in all the unit tests is not a mistake but it may prone to mistake (error) so please avoid this and try to define your setup code in a setup procedure supported by unit testing frameworks like tSQLt.
在所有单元测试中重复设置代码不是一个错误，但是可能会出错（错误），因此请避免这种情况，并尝试在单元测试框架（如tSQLt）支持的设置过程中定义您的设置代码。
8.应用了约束SQL单元测试 (8. SQL unit testing with constraints applied)
In a typical scenario when you are writing SQL unit test for a stored procedure which manipulates a table then you don’t care much about whether the constraints on the table should be removed or not for the unit test.
在典型的情况下，当您为操作表的存储过程编写SQL单元测试时，您不太在乎是否应该为单元测试删除表上的约束。
If your SQL unit test interacts with a table with constraints such as primary key intact then you are diverting from database unit testing standards because of the following facts:
如果您SQL单元测试与具有约束（例如完整的主键）的表进行交互，那么您将因为以下事实而偏离数据库单元测试标准：
- A table in unit testing should not include its constraints 单元测试中的表不应包含其约束
- Constraints on the table should be unit tested separately one by one 表上的约束条件应一一单独地进行单元测试
TSQLt framework provides stubs to mock actual tables with no data and constrains so that they can be readily unit tested.
TSQLt框架提供了存根来模拟没有数据和约束的实际表，以便可以对它们进行单元测试。
9.将SQL单元测试与数据库生命周期管理（DLM）隔离 (9. Isolating SQL unit tests from database lifecycle management (DLM) )
If your Database Lifecycle Management (DLM) strategy does not include your SQL unit tests then this is again a mistake since the purpose of creating SQL unit tests is to become part of Database Lifecycle Management (DLM).
如果您的数据库生命周期管理（DLM）策略不包括SQL单元测试，那么这又是一个错误，因为创建SQL单元测试的目的是成为数据库生命周期管理（DLM）的一部分。
This means the SQL unit tests ideally should run in the following two stages:
这意味着理想情况下，SQL单元测试应在以下两个阶段中运行：
- Manually when deploying changes to debug database 在将更改部署到调试数据库时手动进行
- Automated when deploying changes to shared dev database and other stages 在将更改部署到共享开发数据库和其他阶段时自动执行
TSQLt offers both manual and automated flavours provided the database developer has experience of working with Database Lifecycle Management (DLM).
如果数据库开发人员具有使用数据库生命周期管理（DLM）的经验，则TSQLt提供手动和自动两种形式。
10.没有适当的清理策略 (10. No clean up strategy in place)
If there is no clean up strategy in place then you are missing a major thing in your SQL unit testing strategy.
如果没有适当的清理策略，那么您将错过SQL单元测试策略中的主要内容。
In the absence of a standard clean up procedure your SQL unit tests might end up in the Production database which is not at all desired and must not be allowed.
在没有标准清理程序的情况下，您SQL单元测试可能会最终出现在Production数据库中，这是完全不希望的，并且绝对不允许。
You must enforce a strategy to drop the tests including the testing framework from the database before the database is moved to the final stage which is production which also demands a very wisely chosen source control strategy.
您必须执行一项策略，以便从数据库中删除包括测试框架在内的测试，然后再将数据库移至最终阶段，即生产阶段，这也需要非常明智地选择源代码控制策略。
TSQLt has built-in framework and tests clean up support.
TSQLt具有内置框架和测试清理支持。
By avoiding the mistakes and adopting their remedies discussed in this article you are not only improving your SQL unit testing skills but also helping your development team setting up better standards in SQL unit testing for your organisation.
通过避免本文中讨论的错误并采取补救措施，您不仅可以提高SQL单元测试技能，而且可以帮助您的开发团队为组织建立更好SQL单元测试标准。
目录 (Table of contents)
| tSQLt –数据库单元测试中被遗忘的宝藏 |
| 简单单词中使用tSQLt进行的常规SQL单元测试 |
| tSQLt单元测试的测试驱动数据库开发（TDDD）基础 |
| 10个最常见SQL单元测试错误 |
| 为什么要为SQL单元测试巧妙地命名数据库对象 |
| 您可以针对任何存储过程编写三个标准SQL单元测试 |
| 使用tSQLt创建SQL单元测试实用程序过程 |
翻译自: https://www.sqlshack.com/10-most-common-sql-unit-testing-mistakes/
@sql 单元测试
2320
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
