如何全方位地给 SQL Server 做测试

点击蓝色“有关SQL”关注我哟

我在知乎上有个付费问答专栏，69元一次。虽然设置了付费，却经常收到咨询，询问数据库行业入门与求职技巧问题。回答几次之后，发现一个共性，大部分朋友对数据库非常感兴趣，但苦于项目经验缺少，迟迟找不到好工作。

那么数据库经验从哪里来呢？最重要的是，真正的实战经验，而非三流培训机构的口水项目。有的培训机构还是相当奥里给，有专职的一对一老师，辅导员。配备的项目，也是从从企业真实项目浓缩出来的。

真正能给初学者练手的项目非常少，如果你得到了，请珍惜。如果确实没有经验，也别灰心，曲径通幽，办法总是有的，比如你可以从数据库测试做起。

数据库测试，给外人看起来非常简单，点点界面，偶尔手工Mockup （模拟），然后比对下数据。但真实的情况，可能远比你想象的复杂。

简单罗列下，数据库测试有这些：

看到这，估计很多朋友都会头皮发麻，这可不比写 SQL 轻松啊。是的，开发不需要去知道这些，所以他们成为了开发，而不需要对数据库容量，压力和性能，高可用负责，而这些恰恰都是企业要考虑的真实需求。

如果建库不考虑容量,执行 create database 的时候，你准备给它分多少硬盘容量，准备采购几块硬盘？数据库几经周折，终于能用了，但现有硬件配置，参数配置，能抗每秒多少并发不知道，那系统正式上线了，会不会崩？

这些都需要测试，当一个测试员并不比开发轻松。

而且测试进阶阶段，除了像开发一样要写测试代码，还有一套测试体系等着你去创建，用它去衡量乙方供应商的软件。比如这些：

那么有同学会纳闷了，看这些内容比开发还难，越发没信心学好数据库了。别急，下面通过实战演示，如何快速地在 SQL Server 中进行全方位的测试。

在演示之前，我先把演示用到的软件罗列下，以方便大家跟着练：

• windows server 2016

• sql server 2019

• SQLToolbeltEssentials

三款软件都需要收费，好在他们都有一段时间的免费期，各自的官网都提供下载。

而这里，最重要的一款软件就是 SQLToolbeltEssentials, redgate 出品，赞！

除了测试套件之外，还有很多其他优秀的功能，比如对比数据集差异，对比 schema 差异等。这些对于 ETL Girl/Boy 来肯定非常高效。

那么我们说说测试步骤：

• 建库

• 建表

• 建sp (即 Stored Procedure:存储过程)

• 建测试用例

• 运行测试

• 解读测试报告

建库-建表-建sp 代码如下：

CREATE TABLE [dbo].[workflow](
    [flowid] [INT] NULL,
    [flowamount] [INT] NULL,
    [flowcount] [INT] NULL
) ON [PRIMARY]
GO



WITH L0
AS (SELECT *
    FROM
    (
        VALUES
            (1),
            (2),
            (3)
    ) AS T (C) ),
     L1
AS (SELECT a.C,
           b.C AS BC
    FROM L0 AS a
        CROSS JOIN L0 AS b),
     L2
AS (SELECT a.C,
           b.C AS BC
    FROM L1 AS a
        CROSS JOIN L1 AS b),
     L3
AS (SELECT a.C,
           b.C AS BC
    FROM L2 AS a
        CROSS JOIN L2 AS b),
     L4
AS (SELECT a.C,
           b.C AS BC
    FROM L3 AS a
        CROSS JOIN L3 AS b),
     L5
AS (SELECT a.C,
           b.C AS BC
    FROM L4 AS a
        CROSS JOIN L4 AS b)
INSERT INTO dbo.workflow
(
    flowid,
    flowamount,
    flowcount
)
SELECT TOP 50000
       RNK,
       RNK * 10,
       RNK + 20
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RNK FROM L5) M;

GO 



CREATE PROCEDURE 
    dbo.workflowAddUpdate 
    @flowid int , 
    @flowamount int , 
    @flowcount int = 1  
AS 
BEGIN 

    IF EXISTS(SELECT TOP 1 1 FROM dbo.workflow WITH(NOLOCK) WHERE flowid = @flowid )
    BEGIN 
        UPDATE workflow SET flowamount = @flowamount ,flowcount= @flowcount 
        WHERE flowid = @flowid  
    END 
    ELSE 
    BEGIN 
        INSERT INTO dbo.workflow
        (
            flowid,
            flowamount,
            flowcount
        )
        VALUES
        (   @flowid, -- flowid - int
            @flowamount, -- flowamount - int
            @flowcount -- flowcount - int
            )
    END 

END 

GO 

测试部分就值得书写下：

当我们安装完毕 SQLToolbeltEssentials，在工具栏会看到这些按钮：

今天的主角是 SQL Test. 它引用的测试框架是 tSQLt，开源。

点开 SQL Test 选择需要测试的数据库 factory, tSQLt 会自动安装一些表，存储过程.所以不建议在生产环境安装 SQLToolbeltEssentials.

随着 tSQLt 安装的是 SQLCop, 它包括了一系列对数据库属性做的测试，这大概是宝藏所在了。它会测试数据库配置的合理性，性能参数配置的正确性，还有数据库服务的安全设置。初学者可以学到很多有意思的内核硬知识。

因 SQLCop 并不执行运行用户代码，所以不影响测试覆盖率。

这里重要的是测试覆盖率，底下有讲

此时，数据库中仅有一张表和一个存储过程，我们要给该存储过程写测试用例，测试程序是不是正常运行。因此需要在 factory 下面增加一个测试组  factorytest：

测试名称( Test Name)需要命名得有一定意义，比如 factory_workflow_insert ，表示在表 workflow 上测试 insert 操作:

框架帮我们自动生成了测试用存储过程，[factorytest].[test factory_workflow_insert]:

我们唯一要做的就是判断什么时候，这个测试用例会失败：

USE [factory]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--  Comments here are associated with the test.
--  For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/
ALTER PROCEDURE [factorytest].[test factory_workflow_insert]
AS

BEGIN
  --Assemble
  --  This section is for code that sets up the environment. It often
  --  contains calls to methods such as tSQLt.FakeTable and tSQLt.SpyProcedure
  --  along with INSERTs of relevant data.
  --  For more information, see http://tsqlt.org/user-guide/isolating-dependencies/

  DECLARE @flowid INT = 1,
        @flowamount INT = 100 


  --Act
  --  Execute the code under test like a stored procedure, function or view
  --  and capture the results in variables or tables.
  EXEC dbo.workflowAddUpdate 
    @flowid = @flowid, 
    @flowamount = @flowamount 


  --Assert
  --  Compare the expected and actual values, or call tSQLt.Fail in an IF statement.  
  --  Available Asserts: tSQLt.AssertEquals, tSQLt.AssertEqualsString, tSQLt.AssertEqualsTable
  --  For a complete list, see: http://tsqlt.org/user-guide/assertions/
  IF NOT EXISTS(SELECT TOP 1 1 FROM dbo.workflow WITH(NOLOCK) WHERE flowid = @flowid)
  BEGIN     
    EXEC tSQLt.Fail 'TODO:Implement this test.'
  END 

END;

这个时候，有了测试用例，就可以开始跑测试了，点下 Run Tests, factorytest 和 SQLCop 会跑起来：

如果写的测试用例没有语法问题，factorytest 下面写好的测试用例都会成功跑完，而 SQLCop 会帮我们把配置错误或不合理的地方抓出来。SQLCop 涉及到 DBA 的范畴，暂且不谈，先说测试覆盖率。

这就是测试覆盖率报告。dbo.workflowAddUpdate 就是要测试的存储过程，可以看到有 3 个测试入口，而我们的测试用例（参数）只测试到了 2 个入口，测试覆盖率就是66.67. 剩下的一个入口没有测到，那么说不到哪天，新的参数传进来了，会出现 bug.

SQL Test 强悍的地方在这里，我们编写的测试用例，测到了哪条分支，会帮我们标绿：

从示意图马上得到这么个消息，还有 insert 没有测完。因为之前初始化数据的时候，flowid = 1 已经插入了，所以第一个测试用例，只是走了第一个分支，第二个分支没有走。为此，我们还要再建一个测试用例，让它走完另一个分支。

USE [factory]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--  Comments here are associated with the test.
--  For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/
ALTER PROCEDURE [factorytest].[test factory_workflow_update]
AS
BEGIN
  DECLARE @flowid INT = 999999,
        @flowamount INT = 100 , 
        @flowcount INT = 10 ;


  --Act
  --  Execute the code under test like a stored procedure, function or view
  --  and capture the results in variables or tables.
  EXEC dbo.workflowAddUpdate 
    @flowid = @flowid, 
    @flowamount = @flowamount , 
    @flowcount = @flowcount


  --Assert
  --  Compare the expected and actual values, or call tSQLt.Fail in an IF statement.  
  --  Available Asserts: tSQLt.AssertEquals, tSQLt.AssertEqualsString, tSQLt.AssertEqualsTable
  --  For a complete list, see: http://tsqlt.org/user-guide/assertions/
  IF NOT EXISTS(SELECT TOP 1 1 FROM dbo.workflow WITH(NOLOCK) WHERE flowid = @flowid)
  BEGIN     
    EXEC tSQLt.Fail 'TODO:Implement this test.'
  END 
END;

再重新运行下测试，看下报告，测试覆盖率就是 100% 了，剩余的代码也都标绿：