This article on SQL Unit Testing is the second part on the series about SSDT and database development
Part I: Continuous integration with SQL Server Data Tools and Team Foundation Server
Introduction
In December 2012, a great addition was made to SSDT: The ability to do SQL unit testing.
However, this feature is not available on the free edition of SSDT. For doing unit test with SSDT you will at least need a Visual Studio Professional edition or above.
As you and the team members make changes to the database schema you can verify that these changes are working as expected or if they have broken existing functionality.
Proceeding
Usually you will want to baseline your database and then run some unit tests on the changes that you have made. Personally, I have made a habit out of taking a snapshot of the database I am working with at different stages and at least once a week. This way I can always go back to the state it was before I made the subsequent changes without having to roll back changes with TFS.
Because SQL unit testing of your database is just a “Unit Test Project”, you can create and run database unit test without a database project. However, the only way to auto-generate tests for specific database objects is to use the database project.
When creating a test project from a database project Visual Studio will automatically generate some of classes for you. Most of the plumbing will be done that way. The most important class in that regard is:
Microsoft.Data.Tools.Schema.Sql.UnitTesting;
Checking for the latest version of SSDT
First you need to check if you have the latest version of SSDT. This is done with the Check for Updates menu under the SQL menu.
Figure 1: Getting the latest version of SSDT
When checking for updates you also have the possibility to let Visual Studio do it automatically for you by selecting automatically check for updates to SQL Server Data Tools.
Figure 2: Automatically checking for new versions
Using Projects in SQL Server Object Explorer (SSOX)
Each time you hit F5, SSDT will deploy your database to your LocalDB. By the way, it is possible to change this behavior by going to the Debug tab of the project’s property page and change the connection string there so your database gets deployed somewhere else than in this local instance.
Creating a SQL Server Unit Test from an existing object in the database
From SSDT You can automatically create stubs for SQL unit testing stored procedures, functions and triggers.
Let us say that we want to test a stored procedure called uspRankCustomer that we created in a previous blog post. See Continuous integration with SQL Server Data Tools and Team Foundation Server for a script for creating the database. Alternatively, use this embedded script with the complete database schema used in this example.
Find the stored procedure you want to create the test stub for and right-click on it in the project node of SSOX (keep in mind that the Projects node will not contain your database project before you have successfully deployed / publish your database).
Figure 3: Creating unit tests from Projects node
In the window that shows next, you can chose if you want to create a VB.Net or a C# test project as well as a list of all the database object that support SQL unit testing and the class name for the test file being created.
Figure 4: Choosing which element to create test for
I chose C# and after project creation, you are presented with a SQL Server Test Configuration dialog where you can specify which database to run the test on even a secondary connection to validate those tests as well as the option to deploy the database prior to you run your tests.
Remark : If you must test views or stored procedures that have restricted permissions, you would typically specify that connection in this step. You would then specify the secondary connection, with broader permissions, to validate the test. If you have a secondary connection, you should add that user to the database project, and create a login for that user in the pre-deployment script.
In my case I want to run my tests on the database I deployed earlier This is how my setup looks like:
Figure 5: Configuring the test project
If you look at the solution explorer you will see that following was created:
- A test project
- A SqlDatabaseSetupFile
- A SQLUnitTest class
- The app.config of the project contains the settings for deployment and database connection
Define test logic
My database is quite simple and contains 3 tables and 3 stored procedures. In my previous post, I created a table and a procedure for ranking customers. I now want to test if my ranking procedure uspRankCustomers works as intended and ranks the customer as specified in the ranking table.
Here is the simple stored procedure used:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
CREATE PROCEDURE [dbo].[uspRankCustomers] AS DECLARE @CustomerId int DECLARE @OrderTotal money DECLARE @RankingId int DECLARE curCustomer CURSOR FOR SELECT c.CustomerID, ISNULL(SUM(oh.OrderTotal), 0) AS OrderTotal FROM dbo.Customer AS c LEFT OUTER JOIN dbo.OrderHeader AS oh ON c.CustomerID = oh.CustomerID Group BY c.CustomerId OPEN curCustomer FETCH NEXT FROM curCustomer INTO @CustomerId, @OrderTotal WHILE @@FETCH_STATUS = 0 BEGIN IF @OrderTotal = 0 SET @RankingId = 1 ELSE IF @OrderTotal < 100 SET @RankingId = 2 ELSE IF @OrderTotal < 1000 SET @RankingId = 3 ELSE IF @OrderTotal < 10000 SET @RankingId = 4 ELSE SET @RankingId = 5 UPDATE Customer SET RankingId = @RankingId WHERE CustomerId = @CustomerId FETCH NEXT FROM curCustomer INTO @CustomerId, @OrderTotal END CLOSE curCustomer DEALLOCATE curCustomer |
Test: For my existing customers I want to update the ranking using the Ranking table.
Step 1: Define the test script
I want to execute my stored procedure and make sure that there are no nulls in the rankingID of the customer table. This way I can be sure that all customers have been ranked!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- database unit test for dbo.uspRankCustomers DECLARE @RC AS INT; SELECT @RC = 0; -- execute the stored procedure EXECUTE @RC = [dbo].[uspRankCustomers] ; -- select the customer ids without any ranking (should be 0) SELECT @RC = CustomerID from Customer where RankingID IS NULL SELECT @RC AS RC; |
In Test conditions delete the automatically created condition and create a new one. Change its type to Scalar Value and in the property window make sure the expected value is 0.
Figure 6: Writing the unit test
Step 2: Running the test
In the Test tab click Windows and then Test Explorer the Test Explorer window should appear, listing your test. Right-click on it and choose Run Selected Test. It should now run and show a green icon if it a success (or a red one in event of a failure).
Figure 7: Successful test run
If you remember the previous post in this series, I made it possible to do continuous deployment with MSBuild and Team Foundation Server. I showed how you are able to build and deploy your database changes for each check-in. Well, it is also possible to have this procedure run the test for you. This is useful for running automated tests and analyze the impact of code changes on your tests as part of your build process.
But for now we’ll disable it by removing it in the Build Definition file. On the left pane choose Process and Click the ellipsis on the right side of Automated Test and click Remove in the dialog that shows !!
Figure 8: Removing test from build process
Running test as a part of your Build Process will be the subject of upcoming blog posts, so stay tuned for more SSDT and SQL unit testing fun!!
Read more about SQL unit testing in Visual Studio: Verifying Database Code by Using SQL Server Unit Tests
His background and work experience mixes technology and business, providing him a unique edge to contribute to datawarehouse and SharePoint projects, being able to effectively interact with technical and business decision makers.
Régis is a frequent speaker at SQL Server conferences and is a board member of the Danish SQL Server User Group and the founder of the SQL Saturday Denmark community event.
In april 2014 Régis received the MVP award for SQL Server for his involvment in the product and the community.
View all posts by Régis Baccaro
- Continuous Deployment using SQL Server Data Tools and Visual Studio Online - February 2, 2015
- Creating Azure automation to start Azure VMs - February 2, 2015
- Deployment to several databases using SQL Server Data Tools and TFS using a Custom Workflow file - January 16, 2015