Sql Server Data Tools Query Designer

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.

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.

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.

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.

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.

Steps

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

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
Add New Stored Procedure to SQL Server Database Project: In this phase, we will add a new stored procedure to database project
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

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.

/wp-content/uploads/2019/02/članak.png

In the Solution Explorer window in SSDT, we will right click the database project name and select database import method in the context menu. In this option, Visual Studio allow us two types of database importing method option. First one is Data-tier Application option which is also known as dacpac. Dacpac is a zipped deployment package which contains the database schema and other database objects definitions in xml files. The most significant benefit of this package is portability so we can deploy our database to any SQL Server instance and also we can deploy dacpac file to Azure SQL. Another method is to connect to database and then import database schema and object definitions from database server. We don't have Azure SQL database dacpac file therefore we will choose this method. Actually, importing database operation process reads database schema and other object definitions from the source database and then creates a dacpac file under bin folder of database project so that Visual Studio performs deployment process through this dacpac file.

SSDT

In the Import Database screen, we will click Select Connection and then connect to Azure SQL database in SSDT with proper credentials.

/wp-content/uploads/2019/02/word-image-126.png

We will navigate and expand Azure tab then fill the credential options of the database connection and then click Connect.

Connection manager in SSDT

And finally, everything is ready to start importing database schema and object definitions from source Azure SQL database to database project. We will click Start in SSDT to begin importing database schema process.

Import database in SSDT

Import database summary

As already we noted that, Visual Studio created a dacpac package under the bin folder and every schema and object details are stored in this file. You can also import this file manually into another database server. We will right click to project and then select Open Folder in File Explorer in SSDT.

Solution explorer

We will navigate to bin -> Debug folder in SSDT so that we can find out DACPAC package.

DACPAC package

Now, we will change the target database property of database project because we will deploy it to Azure SQL. Right click in the database project and then select Properties option in SSDT and then change the target platform to Microsoft Azure SQL Database V12

Project settings

We will rebuild project in order to check errors in the project and check on the output of the project.

Project output

Adding a new stored procedure to SQL Server database project in SSDT

In the solution explorer, all of the database objects will be imported after the schema importing.

Solution explorer

In this part, we will add a new stored procedure under SalesLT schema which inserts new a new row to ProductCategory table. Right click into stored procedure folder in the Solution Explorer panel in SSDT and then add new item.

Add new item

Chose the Stored Procedure in the object list and then give a name to this stored procedure.

Programmability in SSDT

We will add the following script in order to create stored procedure.

CREATE PROCEDURE [ SalesLT ] . [ SetProcductCat ]

@ ParentProductCategoryId AS INT ,

@ Name AS VARCHAR ( 50 )

INSERT INTO SalesLT . ProductCategory ( ParentProductCategoryID , Name )

VALUES ( @ ParentProductCategoryId , @ Name )

Create procedure

Now, we will make our first publish to Azure SQL database. Right click in the project and then select Publish.

Publish solution

Publish Database window in SSDT will be open. In this window, we can configure the target database connection and save this configuration through Create Profile option.

Publish database

When you click Create Profile button, Visual Studio automatically creates publish.xml file which stores the credentials and other options. When you double click this file Publish Database screen will be reappear.

Publish database setting

The publish operation will be completed successfully.

Completion message in Publish

Creating SQL Unit Testing in SSDT

In this section, we will focus to how to create and configure SQL unit testing project with help of SSDT. In the previous parts we created a database project and then we added a new stored procedure. However, we don't have any idea about this stored procedure functional behaviors. For this reason, we need to add unit test to check its behavior. In generally, SQL unit testing is based on three phases and these are;

Pre-Test: In this step we can check any required conditions before the test method. For our test, we will delete particular rows which we will insert later in unit test
Test: In this step, we can specify and add the test conditions
- I want to add a tip in here, you have to add a well-defined and clear explanations comments to test method because this description will help other developers to figure out to main idea of the test method. On the other hand, a well-prepared test documentation will take a significant advantage during to unit test development process because it will be hand-guide of unit test
Post-Test: In this step, we can specify required operations after the test method

Now, let's return to our SQL unit testing demonstration. We will right click to stored procedure and click Create Unit Test which we created in the previous steps. In the Create Unit Tests screen we will give a name to unit test project and select programing language of the test project. We can select C# or Visual Basic in SSDT.

Create SQL Unit Tests

After these steps, SQL Server Test Configuration screen will appear. In this screen we will specify the connection string for test project and also we will check to automatically deploy the database project before unit tests are run. This option allows us to deploy the database project before running unit test.

Test Configurations

This setting is stored into app.config file of test project so that you can edit this setting according to yourself.

Visual studio integration

Visual Studio SSDT will automatically create a test template however we need to change this unit test method. We will develop the test method according to following sample test document.

test template

We will select Pre-test option in the test condition combobox then add the following query. We will also add a little definition regarding to test document.

If test values exist in the table delete this values in Pre-Test

Test Values :

@ParentProductCategoryId =14

@Name =NewProductCategory

DECLARE @ ParentProductCategoryId AS INT = 14

DECLARE @ Name AS VARCHAR ( 50 ) = 'NewProductCategory'

DELETE FROM SalesLT . ProductCategory WHERE ParentProductCategoryID = @ ParentProductCategoryId

AND Name = @ Name

SQL unit test

Visual Studio offers various predefined test conditions. These are:

Data Checksum: This test condition compares expected result and set row values and resultset values which returned from test. If this resultsets values don't match it will be a fail.

Empty ResultSet: This test condition expects empty result set if tested.

Execution Time: This test condition compares the tested method execution time and expected execution time. If tested method execution time takes longer than the expected execution time, it will be fail. The default value of this test condition is 30 second.

Expected Schema: This test condition compares expected result set schema (columns and data types) and result set schema which returned from test. If this resultsets schema don't match, it will be fail.

Not Empty ResultSet: This test condition expects non-empty result set from test method.

Row Count: This test condition compares resultset row number and expected row number.

Scalar Value: This test condition compares expected value to particular value which returns from test method. In this test method we can configure column number and row number. Now, we will change the combobox to Test.

The test condition must be Row Count because we have to check to insert functionality. In the Row Count condition, the Row Count property must be set as 1.

Test SetProductCatUnitTest stored procedure with test values

and then check the test values in SalesLT.ProductCategory

Test Values :

@ParentProductCategoryId =14

@Name =NewProductCategory

DECLARE @ IdentityNum AS INT , @ ParentProductCategoryId AS INT , @ Name AS VARCHAR ( 50 ) ;

SELECT @ IdentityNum = 0 ,

@ ParentProductCategoryId = 14 ,

@ Name = 'NewProductCategory' ;

EXECUTE [ SalesLT ] . [ SetProcductCat ] @ ParentProductCategoryId , @ Name ;

SELECT @ IdentityNum = @ @ IDENTITY ;

SELECT * from SalesLT . ProductCategory where ProductCategoryId = @ IdentityNum

AND Name = @ Name

AND ParentProductCategoryId = @ ParentProductCategoryId

predefined test conditions

Lastly, we will add Post-Test method in SSDT and then delete the inserted values from table.

Delete test values in SalesLT.ProductCategory in Post-test

Test Values :

@ParentProductCategoryId =14

@Name =NewProductCategory

DECLARE @ ParentProductCategoryId AS INT = 14

DECLARE @ Name AS VARCHAR ( 50 ) = 'NewProductCategory'

DELETE FROM SalesLT . ProductCategory WHERE ParentProductCategoryID = @ ParentProductCategoryId

AND Name = @ Name

predefined test conditions

Let's run our unit test. In Visual Studio SSDT you will find Test Menu or Test explorer so that you can run the unit test. Let's use test explorer and then click Run All test method.

predefined test and deploy solutions

At first, Visual Studio SSDT deploys the project to target database and then it will run unit test methods.

Visual Studio deploys to target database

Now, we will add Expected Schema test condition to check result set column names and datatypes.

We will add new test condition which is expected schema and then click Configuration (…) button in the properties of the expected schema test condition.

Expected Schema

We will click a Select Connection… and configure a database connection then we will set the expected resultset columns and datatypes through the following query. In this point, the main idea is to compare expected resultset schema with test method resultset schema. According to our stored procedure, inserted row columns and datatypes have to match any row of the SalesLT.ProductCategory table.

We will use the following query in the query and then click Retrieve in SSDT.

SELECT TOP 1 * FROM SalesLT . ProductCategory

Configuration for test conditions

We will re-run the test so that click Run All.

Run All solutions

Finally, our all test method passed.

Conclusion

In this article we mentioned about SQL unit testing in SSDT and also we learnt how to develop and run unit testing in Visual Studio. SQL unit testing implantation provides various advantages to us such as improve coding quality, reduce development costs and degrade the defects of database schema and objects.

Author
Recent Posts

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec