Click here to Skip to main content
15,867,453 members
Articles / DevOps / Unit Testing

SQL Unit Testing with SQL Server Data Tools (SSDT)

Rate me:
Please Sign up or sign in to vote.
4.64/5 (5 votes)
8 Feb 2018CPOL9 min read 30.8K   394   18   4
SQL unit testing with SQL Server data tools (SSDT)

What This Article Contains?

This article explains how to use SQL unit testing feature to make the application more robust. Although we can use sample AdventureWorks db provided by Microsoft, it is too big for this article and we will be creating a very simple database to avoid confusion and cover all the topics. DB Scripts are attached wherever required, just copy and execute in your version of SQL Server.

What This Article Doesn’t Contain?

It doesn’t contain implementation steps with Visual Studio 2015 or before, although same steps may work for VS 2015 or earlier versions.

What is SQL Unit Testing?

SQL Unit testing refers to standalone unit testing of data, schema, stored procedures, functions, views and triggers of database. Like Code unit testing through NUnit or Visual Studio test cases, UI testing with Coded UI, SQL testing also plays an important role in making an application robust and error-free.

SQL Unit testing is now available with community editions of VS 2015 and VS 2017, which is one more reason to use this. Earlier, this feature was available only with paid version of VS. It is available since the release of VS 2010 professional + edition.

Software Requirements

Visual Studio Community 2017, SQL Server 2012, and SQL Server Data Tools (SSDT). SSDT is a free tool and can be downloaded without providing any sign-in credentials.

Note that, SSDT corresponding to Visual Studio version should be installed, i.e., if you are using VS 2015, install SSDT version available for VS 2015. If you are using VS 2013, install SSDT which supports VS 2013. The older version of installed SSDT will not work with new version of Visual Studio.

It is recommended to use SQL Server 2012 + and VS 2012 +.

2. Create a Database and Add Tables

Create a database with name "DBUnitTesting". And then, execute the attached script. If you want to keep different name of database, modify script and replace "DBUnitTesting" with a new name.

Following is the Database Diagram of table structure and relationships. Three tables are created: Company, Department, and SubDepartment. Here, CompanyId is used in Department table as a Foreign key reference, and DepartmentId is used in SubDepartment table as a Foreign key reference. SubDepartmentLog table is used for audit purposes. Audit tables shouldn’t contain any foreign key link.

Image 1

Create Tables Script

Use "CreateTables.txt" from attached "Scripts" zip folder.

3. Create a Project in Visual Studio 2017

Go to File-> New->Project-> SQL Server-> SQL Server Database Project. This option will be displayed if corresponding SSDT is installed properly on the system. Enter any name, say "SQLUnitTestingDB".

Image 2

Right click and select "Import" -> Database. Provide valid credentials and click "Start".

Image 3

In few seconds, schema will be imported to the code. Click "Finish".

Image 4

Add new project -> class (.NET Framework), name it "TestCases". Add the following references:

  1. Add reference of "SQLUnitTestingDB" project created earlier, because it contains schema detail.
  2. Add Nuget package - Microsoft.VisualStudio.QualityTools.UnitTestFramework.Updated

Right-click, Add New Item, select SQL Server, and then select "SQL Server Unit Test". Since, we will test Company table first, name it "CompanyUnitTest".

Image 5

Why We Created a Separate Class?

We can also add a SQL Server Unit Test in "SQLUnitTestingDB" project but it is a good practice to separate unit test cases from the project which contains schema information. This helps in better maintenance of projects in the long-run when several test cases are added.

3. Create First Unit Test

Go to "CompanyUnitTest.cs" and rename:

  1. "SqlTest1" to "RowCountCompany"
  2. "SqlTest1Data" to "RowCountCompanyData"

The proper naming helps in better maintenance of project. "RowCountCompany" will reflect in test explorer.

Image 6

Image 7

Double click "CompanyUnitTest.cs", designer will open. There are three options: Pre-Test, Test, Post-Test options visible in designer.

Pre-test: It is generally used, but not limited to, to set certain conditions required before actual test is executed.

Test: The main command or operation.

Post-Test: It is generally used, but not limited to, if we need to clean up table data after performing the operation.

It is not mandatory to use all three options. For now, we will use Test option. Double click "CompanyUnitTest" and designer will open. On top left, modified name of test case "RowCountCompany" is visible.

Image 8

Now, consider, as per business rule, database should have four companies. Please note, unit test should not be written according to actual data present in database but in accordance with business rules specified in business requirement specification document. This is very – very important, otherwise it will fail the objective of writing unit test cases. Our objective is to compare business requirement document with actual data present and to measure the deviation. This is a very common mistake made by developers, when they start writing unit test cases for the first time, so avoid it.

Click on "click here to create" link and add the SQL query. Delete "Data Checksum" condition and add scalar value using "+" sign. As soon as it is added, property box is opened, fill 4 in "Expected Value" Column.

Image 9

Save and open Test Explorer through Test->Windows->Test Explorer. Right-click and click "Run Selected Test". Since data is correct in database, it will pass.

Image 10

Now, suppose, there is incorrect data in database. There are more companies added in database, but not specified in business requirement specification document.

Image 11

Run test again in test explorer. It will fail and specify the reason also. This is how we know that our data is incorrect and must be fixed.

Image 12

4. Unit Test Options

SQL unit test provides multiple options such as:

  1. Data Checksum: This ensures that data in table has not changed.

    Click on "Press to configure" in properties tab. Click "Select Connection" and select table. Click Retrieve and data is visible. Click ok.

    Image 13

    Run test case in test explorer. It will pass. During development, suppose, "Toboc" is erroneously modified to "Tobo" in Row 4. Run test again and it will fail. So, Data checksum is ensuring data integrity. Correct "Tobo" to "Toboc" and the test will pass again.

    Image 14

  2. Expected Schema: This checks the schema, not the data as "Data checksum" was doing. Even if data is modified but schema is intact, this test case will pass.

    Select "Press to configure" option and Click "Select Connection", select table. Click Retrieve and data is visible. Click ok.

    Image 15

    To demo this, again update "Toboc" to "Tobo" and run all the cases. We can see that "CompanyExpectedSchemaTest" has passed while "CompanyDataChecksumTest" has failed.

    Image 16

    Add new column in company table, thus modifying the schema.

    Image 17

    Run test again and it will fail. This option ensures schema is maintained.

  3. Row count: Set "RowCount" as 4 in properties. Run test.

    Image 18

  4. Execution Time: It is used to check performance of query or stored procedure (discussed later in the document). Select "Execution time" as option and keep 00:00:00.0010000. We can see error because execution time taken is more than expected.

    Image 19

  5. Empty Result Set: This is used to verify if empty result set is received from database. If greater than 0 rows are returned, test fails.

    Image 20

  6. Not Empty Result Set: This is used to verify if empty result set is received from database. If no rows are returned, test fails. E.g. In case of error in stored procedure or query.

    Image 21

    Both "Empty Result Set" and "Not Empty Result Set "are verifying the result set, but in a different way. Property "ResultSet" is read only in both the cases and can’t be modified.

  7. Inconclusive: This is a default test condition, whenever new test case is added. This test condition is included to indicate that test verification has not been implemented. Delete this test condition from your test after you have added other test conditions.

5. Understanding How Code Behind Works

Now, since we have a good understanding about the number of options provided in SQL unit testing mechanism, we will see how it works in code behind, i.e., in class.

SQL Designer makes it easy for us to write and visualize test cases but in class, it behaves the same as the code unit test cases. Refer to "CompanyRowCount" SQL unit test case. It has two files: resx and cs. Resx files contains the query details while cs class contains the expected output.

Resx File

Image 22

Cs File

Image 23

Debugging Test Cases

Modify condition of expected total row count to 3. Select a test case from Test Explorer. Right- click and select "Debug Selected Tests". As conditions don’t match, error is thrown with details. The behavior is same as Assert in code unit test.

Image 24

Disabling Test Cases

If we set Enabled: False, test case won’t be tested during execution and is shown as passed. This option is used when test cases are written but work on corresponding functionality is still on but not completed. Later, as the project progresses, we can enable and execute all the test cases.

Image 25

6. Verifying Stored Procedures, Functions, Views and Triggers

Stored Procedure

Create a stored procedure in SQL Server which inserts a new row in department table.

Stored Procedure Script

Use "StoredProcedure.txt" from attached "Scripts" zip folder.

Add new test case "DepartmentAdd".

  1. Select "Pre-test" and count number of records in department table and set expected value as 6.

    Image 26

  2. Select "Test" and use stored procedure to insert data in Department table.

    Image 27

  3. Select "Post Test". Verify count again and then delete the data. Generally, post-test is used to clean-up the data used for testing purposes. In case we don’t delete the inserted row, next time test case will fail because row count is 7 and pre-test case says row count should be 6.

    Image 28

So, it is important that while testing stored procedures, etc., we should clean the temporary data inserted and keep it ready for next time. Our purpose of verification of Stored Procedure is achieved.

Function: Create a function "GetSearchTermCount". The function brings the count of records based on search term (performs like operation).

Function Script

Use "Function.txt" from attached "Scripts" zip folder.

Create a new test case and execute function. As "Software Development" and "Management" consists of "ent", 2 records are fetched.

Image 29

Views

Create a View "FetchDetails".

View Script

Use "View.txt" from attached "Scripts" zip folder.

Create a new test case and execute view. Verify row count as ‘9’.

Image 30

Triggers

Create a trigger to update "SubDepartmentLog" table if "SubDepartment" table is updated.

Trigger Script

Use "Trigger.txt" from attached "Scripts" zip folder.

Create a new test case and update department table. Count rows in "SubDepartmentLog" table. A new value is inserted because trigger is executed once record in "SubDepartment" is updated. And hence, row count returned is 1.

Image 31

Image 32

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Gaja Digital agency
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionHow to use SQLCMDVariable in the resx file? Pin
ZeroZeroSvn12-Feb-18 15:55
ZeroZeroSvn12-Feb-18 15:55 
I want to use user-defined SQLCMDVariable in my test query.
I was wondering how to replace it with a real value probably at build time?
Thanks
AnswerRe: How to use SQLCMDVariable in the resx file? Pin
RahulMittal201821-Feb-18 0:22
RahulMittal201821-Feb-18 0:22 
QuestionHi, thanks for this article Pin
Member 136691968-Feb-18 18:38
Member 136691968-Feb-18 18:38 
AnswerRe: Hi, thanks for this article Pin
RahulMittal201821-Feb-18 0:23
RahulMittal201821-Feb-18 0:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.