使用SSDT进行SQL单元测试（SQL Server数据工具）

In this article, we will discuss the essentials of the SQL unit testing and then we will explore how to apply SQL unit testing methodology in Azure SQL Database with help of SQL Server Data Tools (SSDT). In particular, this article will focus about unit test theory in terms of SQL Server database development aspect and illustrate SQL unit testing with a detailed example.

在本文中，我们将讨论SQL单元测试的要点，然后将探索如何借助SQL Server数据工具（SSDT）在Azure SQL数据库中应用SQL单元测试方法。 特别是，本文将从SQL Server数据库开发方面着重于单元测试理论，并通过一个详细的示例说明SQL单元测试。

单元测试： (Unit Test:)

Unit testing is used to check basis functionality and behavior of the smallest and independent part (function, method or etc.) of the code blocks that performs certain operations. In modern software development approaches, unit testing plays a key-role because unit tests directly affects code quality. On the other hand, when we look at unit testing in terms of continuous integration and continuous development approach, the entire application code can be tested before the production deployment process with help of unit test methods. If any unit test returns fail, we can automatically cancel the deployment process.

单元测试用于检查执行某些操作的代码块的最小和独立部分（功能，方法等）的基本功能和行为。 在现代软件开发方法中，单元测试起着关键作用，因为单元测试直接影响代码质量。 另一方面，当我们从持续集成和持续开发方法的角度看待单元测试时，可以在生产部署过程之前借助单元测试方法对整个应用程序代码进行测试。 如果任何单元测试返回失败，我们可以自动取消部署过程。

数据库单元测试： (Database Unit Test:)

Now let’s review the basic concept of unit testing concept with a focus on SQL unit testing. The SQL unit testing approach allows us to test individual and programmable part of database objects such as stored procedures, functions, triggers and schema. The advantage of SQL unit testing is to develop more robust database designs, because these objects have already been checked before production deployment, so SQL unit testing process allows us to minimize the errors which are related to database objects. Assume that we are developing a stored procedure and we know that it will not return any rows, so we can configure a test condition to verify this behavior of the stored procedure and then it can be tested according to this condition as part of a review of the database, or latest database changes.

现在，让我们回顾一下单元测试概念的基本概念，重点是SQL单元测试。 SQL单元测试方法使我们可以测试数据库对象的各个部分和可编程部分，例如存储过程，函数，触发器和架构。 SQL单元测试的优点是开发更健壮的数据库设计，因为在生产部署之前已经检查了这些对象，因此SQL单元测试过程使我们能够最大程度地减少与数据库对象相关的错误。 假设我们正在开发一个存储过程，并且知道它不会返回任何行，那么我们可以配置一个测试条件以验证该存储过程的这种行为，然后可以根据此条件对其进行测试，作为对的回顾。数据库或最新数据库更改。

先决条件 (Prerequisites)

In this article we will use the following tools:

在本文中，我们将使用以下工具：

Visual Studio 2017: It is an integrated development environment (IDE) which allows us to develop various project types in different programing languages.

Visual Studio 2017 ：这是一个集成开发环境（IDE），使我们可以使用不同的编程语言开发各种项目类型。

SQL Server Data Tools: It is also known as SSDT, another required tool to develop SQL unit testing projects. SSDT allows us to develop SQL Server database projects and it also provides Analysis Service, Reporting Service and Integration Service projects.

SQL Server数据工具 ：也称为SSDT，这是开发SQL单元测试项目的另一个必需工具。 SSDT允许我们开发SQL Server数据库项目，还提供分析服务，报表服务和集成服务项目。

Azure SQL: It is a cloud based relational database that can be use database as a service so that we can use Azure SQL database without any physical hardware or on premise-installation.

Azure SQL ：这是一个基于云的关系数据库，可以将数据库用作服务，因此我们可以使用Azure SQL数据库而无需任何物理硬件或在内部安装。

脚步 (Steps)

The following SQL unit testing demonstration will be completed in the below three phases:

下面SQL单元测试演示将在以下三个阶段中完成：

1. Import Azure SQL Database to SQL Server Database Project: In this phase, we will connect to Azure SQL database and import Azure SQL database objects to database project 将Azure SQL数据库导入到SQL Server数据库项目：在此阶段，我们将连接到Azure SQL数据库并将Azure SQL数据库对象导入到数据库项目
2. Add New Stored Procedure to SQL Server Database Project: In this phase, we will add a new stored procedure to database project 将新的存储过程添加到SQL Server数据库项目：在此阶段，我们将新的存储过程添加到数据库项目
3. Create SQL Unit Testing: In this last phase, we will develop a SQL unit test project and then we will test a stored procedure which was added in the previous phase 创建SQL单元测试：在最后一个阶段，我们将开发一个SQL单元测试项目，然后测试上一个阶段中添加的存储过程。

将Azure SQL数据库架构导入到SSDT中SQL Server数据库项目 (Importing an Azure SQL Database Schema to a SQL Server Database Project in SSDT)

In this first step, we will launch Visual Studio and then add a new SQL Server Database Project in SSDT. We will give a name to our database project and then check Create directory for solution. After that click OK so that our database project will be saved to specified location. If you check Create new Git repository, it allows us to track and save our project into Git source control.

在第一步中，我们将启动Visual Studio，然后在SSDT中添加一个新SQL Server数据库项目。 我们将为数据库项目命名，然后选中“ 创建目录”以获取解决方案。 之后，单击“ 确定”，以便我们的数据库项目将保存到指定位置。 如果您选中创建新的Git存储库 ，它使我们可以跟踪项目并将其保存到Git源代码管理中。

In the Solution Explorer window in SSDT, we will right click the database project name and select database import met