SQL Server Code,Tips and Tricks, Performance Tuning: unit testing

Showing posts with label unit testing. Show all posts

Wednesday, May 11, 2016

How to disable tests in tSQLt

The tSQLt unit test framework that we use to do our DB tests does not allow for disabled tests, this is because each test is just a stored proc. Right now the only way is to either rename the proc so it doesn’t start with test or to remove the test

I was thinking about this in the shower this morning….here is what I came up with

Here is a quick way how to prevent a test from running when the tSQLt tests run.
Put the code below into each unit test that you want disabled, you should put this code right after the CREATE PROCEDURE ProcName AS part

This code will pick the correct name and schema, so you can use the same exact code in each test

DECLARE @IsTestDisabled bit = 1 -- set this to 1 if you don't want the test to run
IF @IsTestDisabled =1
BEGIN
 DECLARE @SchemaName varchar(1000)
 SELECT @SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'
 FROM sys.procedures WHERE object_id = @@procid
 PRINT 'Disabled Test: ' + @SchemaName + QUOTENAME(OBJECT_NAME(@@procid))
 RETURN
END

So if we grab the example test from the tSQLt website

We would change it from this

CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS
BEGIN
 DECLARE @actual MONEY;
 DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
 DECLARE @amount MONEY; SET @amount = 2.00;
 SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);
 DECLARE @expected MONEY; SET @expected = 2.4; --(rate * amount)
 EXEC tSQLt.AssertEquals @expected, @actual;
END;
GO

To this

CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS
DECLARE @IsTestDisabled bit = 1 -- set this to 1 if you don't want the test to run
IF @IsTestDisabled =1
BEGIN
 DECLARE @SchemaName varchar(1000)
 SELECT @SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'
 FROM sys.procedures WHERE object_id = @@procid
 PRINT 'Disabled Test: ' + @SchemaName + QUOTENAME(OBJECT_NAME(@@procid))
 RETURN
END
BEGIN
 DECLARE @actual MONEY;
 DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
 DECLARE @amount MONEY; SET @amount = 2.00;
 SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);
 DECLARE @expected MONEY; SET @expected = 2.4; --(rate * amount)
 EXEC tSQLt.AssertEquals @expected, @actual;
END;
GO

Now you will get the following in your tSQLt test run output

Disabled Test: [testFinancialApp].[test that ConvertCurrency converts using given conversion rate]

This will be right above this text

+----------------------+
|Test Execution Summary|
+----------------------+

I want this printed so we know we have disabled tests when we get a email whenever Jenkins kicks off these tests…..

To enable the test again, all you have to do is change the value from 1 to 0 here. So instead of

DECLARE @IsTestDisabled bit = 1

You would make it

DECLARE @IsTestDisabled bit = 0

To see all my tSQLt posts, visit this link: http://sqlservercode.blogspot.com/search/label/tSQLt

Posted by Denis at 11:39 AM No comments:

Email This BlogThis! Share to X Share to Facebook Share to Pinterest

Labels: tSQLt, unit testing, unit tests

Monday, January 25, 2016

The one change I make to each tSQLt database

Whenever I put the tSQLt database on a server, I make sure that I make one change to a stored procedure that ships with tSQLt. The reason for this is that we run our tSQLT tests as part of our automated build process. If any of the tests fail, we developers who are part of that particular project will get an email telling us what the results of the tests were.

The main proc that runs the unit tests always returns 0, I need a way to know if any tests failed or not, if we had failures, we want the email to go out, if there were no failures then we don't want the email to go out.

Here is how I modified the RunAll stored procedure

ALTER PROCEDURE [tSQLt].[RunAll]
AS
BEGIN
 DECLARE @TestResultFormatter NVARCHAR(MAX);
 SELECT @TestResultFormatter = tSQLt.GetTestResultFormatter();
 
 EXEC tSQLt.Private_RunAll @TestResultFormatter;
 -- Added to return -1 instead of the default 0
 IF EXISTS (SELECT * FROM tSQLt.TestResult WHERE result = 'Failure')
 RETURN -1
END;

I added the stuff on lines 9 till 11. I am checking if there were any tests that failed, these tests will have a result of Failure. If any tests failed, the stored procedure will return -1. Now we can easily check this value to determine if we should send the email or not.

I could have also checked if there were any tests which had a result which was not Success instead. A test could also have a status which is Error, you will get such a status if for example an object doesn't exist in the database.

There you have it, this is the change I made. Maybe you had the same problem and this will help you as well.

Posted by Denis at 12:21 PM No comments:

Email This BlogThis! Share to X Share to Facebook Share to Pinterest

Labels: tSQLt, unit testing

Monday, October 12, 2015

Some reasons your tSQLt test is not executing

This post is part of a Unit Testing series I created, you can find links to all the posts here: Unit testing in SQL Server, a list of all the posts
Today we are going to look at why a test might not be running. Before starting, if you don't have tSQLt setup, follow the instruction here: Unit testing in SQL Server, installing tSQLt and doing a quick test
First, if you didn't create a test database yet, let's create one and name it UnittestExamples, we will also create a simple table in that database

CREATE DATABASE UnittestExamples
GO
USE UnittestExamples
GO
CREATE TABLE SomeTable (ID int)
Now connect to your tSQLt database
USE tSQLt
GO

Let's create two test classes

EXEC tSQLt.NewTestClass 'MyNewClass'
EXEC tSQLt.NewTestClass 'MyNewestClass'

To see all the test classes in your tSQLt database, you can use the following query

SELECT Name, SchemaId
FROM tSQLt.TestClasses;

Name SchemaId
MyNewClass6
MyNewestClass7

Now we will create a bunch of procs, these procs will test for the existance of a table in the UnittestExamples database

CREATE PROCEDURE MyNewClass.[ObjectExist]
AS
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable'
GO
CREATE PROCEDURE MyNewestClass.[ObjectExist]
AS
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable'
GO
CREATE PROCEDURE MyNewClass.[test ObjectExist]
AS
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable'
GO
CREATE PROCEDURE MyNewestClass.[test ObjectExist]
AS
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable'
GO
CREATE PROCEDURE [test ObjectExist]
AS
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable'
GO

Now it is time to run our tests,

EXEC tSQLt.tSQLt.Run 'MyNewestClass'

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name |Result |
+--+----------------------------------+-------+
|1 |[MyNewestClass].[test ObjectExist]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

EXEC tSQLt.tSQLt.Run 'MyNewClass'

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name |Result |
+--+-------------------------------+-------+
|1 |[MyNewClass].[test ObjectExist]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

Now, let;s call all the classes in one shot, will that make a difference?

EXEC tSQLt.tSQLt.RunAll

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name |Result |
+--+-------------------------------+-------+
|1 |[MyNewClass].[test ObjectExist] |Success|
|2 |[MyNewestClass].[test ObjectExist] |Success|
-----------------------------------------------------------------------------
Test Case Summary: 2 test case(s) executed, 2 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

Nope, only two out of five tests executed.
So what happened, why are only two of the five tests running? The reason is that each test has to be named so that the first part of the name is test. We created the following four test stored procedures

CREATE PROCEDURE MyNewClass.[ObjectExist]
CREATE PROCEDURE MyNewestClass.[ObjectExist]
CREATE PROCEDURE MyNewClass.[test ObjectExist]
CREATE PROCEDURE MyNewestClass.[test ObjectExist]

As you can see the first two are not named so that the name starts with test. Renaming those so that they start with test will fix that. I myself have made this mistake many times in the beginning, hopefully this will save someone some time in the future

What about the 5th proc why didn't that run?

CREATE PROCEDURE [test ObjectExist]

As you can see that proc is named correctly, however it is not in the two test classes that we created. It is created in the default class which most likely will be dbo, You can actually execute this by calling the dbo class

EXEC tSQLt.tSQLt.Run 'dbo'

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name |Result |
+--+------------------------+-------+
|1 |[dbo].[test ObjectExist]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

So there you have it, the procs have to be named so that they start with test and the procs have to be created in a test class that the following query returns

SELECT Name, SchemaId
FROM tSQLt.TestClasses;

Time to clean up the mess...... Here is how you can quickly clean up, this will drop the test class as well as all the objects in that class

EXEC tSQLt.DropClass 'MyNewClass'
EXEC tSQLt.DropClass 'MyNewestClass'

This post is part of a Unit Testing series I created, you can find links to all the posts here: Unit testing in SQL Server, a list of all the posts

Posted by Denis at 10:46 AM No comments:

Email This BlogThis! Share to X Share to Facebook Share to Pinterest

Labels: tSQLt, unit testing, unit tests

Monday, October 05, 2015

Unit testing in SQL Server, a list of all the posts

This is a list of all the unit test blog post I created about tSQLt and unit testing.

Unit testing in SQl Server... why?
This post explains why you should be unit testing

Unit testing in SQL Server..what are unit tests
This post explains what unit tests are

Unit testing in SQL Server, taking a first look a tSQLt
This post takes a first look at tSQLt, tSQLt is a unit test framework completely written in T-SQL

Unit testing in SQL Server, installing tSQLt and doing a quick test
This post helps you install tSQLt and run a few test to get a quick demo of how tSQLt works

Some reasons your tSQLt test is not executing
This post will explain why some unit tests might not be running

Posted by Denis at 4:24 PM No comments:

Email This BlogThis! Share to X Share to Facebook Share to Pinterest

Labels: tSQLt, unit testing, unit tests

Unit testing in SQL Server, installing tSQLt and doing a quick test

In today's post we will install tSQLt and run a simple test. To start download tSQLt, unzip the zipfile into its own folder. Here is what is inside that zip file, we are looking at executing the tSQLt.class.sql file later on

Open up SSMS, connect to the database server where you want to create the tSQLt database. We need to enable the CLR on the server where you are running your tests because tSQLt is using some CLR functionality.

Execute the following command to enable the CLR on your box

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Now we need to create the database and make it trustworthy, execute the following

CREATE DATABASE tSQLt
GO
USE tSQLt
GO
ALTER DATABASE tSQLt SET TRUSTWORTHY ON;
GO

Go to your unzipped folder, open up the tSQLt.class.sql file, make sure that you are connected to you tSQLt database, execute the code

You should see something like this in the output once all the code has run

Installed at 2015年10月05日 17:58:53.360

(1 row(s) affected)

+-----------------------------------------+
| |
| Thank you for using tSQLt. |
| |
| tSQLt Version: 1.0.5686.18945 |
| |
+-----------------------------------------+

After the code has completed, run the following

EXEC tSQLt.Fail 'congratulation, we are ready to go to the next step'

You should see the following output

Msg 50000, Level 16, State 10, Procedure Fail, Line 51
tSQLt.Failure

Let's create a sample database so that we can do some testing

Execute the following code

CREATE DATABASE UnittestExamples
GO
USE UnittestExamples
GO
CREATE TABLE dbo.test1(id int)
CREATE TABLE dbo.test2(id int)
INSERT dbo.test1 VALUES(1)
INSERT dbo.test2 VALUES(2)
INSERT dbo.test1 VALUES(3)
INSERT dbo.test2 VALUES(3)
INSERT dbo.test1 VALUES(4)
INSERT dbo.test2 VALUES(5)
GO

USE tSQLt
GO

Now let's run a couple of tests

tSQLt ships with an assertion to test if 2 tables are the same, the assertion is named AssertEqualsTable

So let's see if these two tables are the same, execute the following

EXEC tSQLt.AssertEqualsTable 'UnittestExamples.dbo.test1', 'UnittestExamples.dbo.test2'

Here is the output
Msg 50000, Level 16, State 10, Procedure Fail, Line 73
tSQLt.Failure

As you just saw, will get an error, now what will happen if you compare the table against itself, run this to find out.

EXEC tSQLt.AssertEqualsTable 'UnittestExamples.dbo.test1', 'UnittestExamples.dbo.test1'

There was no error now, this is expected

What happens if we test a table against a non existing table?

EXEC tSQLt.AssertEqualsTable 'UnittestExamples.dbo.test1', 'UnittestExamples.dbo.test112'

Msg 50000, Level 16, State 10, Procedure Fail, Line 51
tSQLt.Failure
Msg 208, Level 16, State 1, Line 2
Invalid object name 'UnittestExamples.dbo.test112'.

I want to point something important out. Comparing two tables is usually doing for small lookup tables, you create a table inside your unit test with values that you expect to match the table in the database, if these tables don't match, then you have a problem. You wouldn't want to compare two million row tables in a test.

Let's do another assertion, let's see if an table exists.

EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable'

Msg 50000, Level 16, State 10, Procedure Fail, Line 51
tSQLt.Failure

That fails as expected, now let's try it with a table that exists

EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.test1'

That ran fine as expected.

Let's clean up by dropping the two tables we created earlier.

DROP TABLE dbo.test1 , dbo.test12

Creating a test class
What you want to do with tSQLt is group all your tests together, for example if you are testing a stored procedure, you can execute just those set of tests. When creating a test class in tSQLt, a new schema will be created, you can easily verify this. Execute the following

USE tSQLt
GO
SELECT * FROM sys.schemas
WHERE schema_id BETWEEN 5 AND 1000

nameschema_idprincipal_id
tSQLt 51

As you can see, the only schema the query returns is the tSQLt schema. Now let's create a new test class, you do this by executing the NewTestClass stored procedure, if we wanted to create a test class named WebFlags, we would do it like this

tSQLt.NewTestClass 'WebFlags'

Running the following code again, you will now see an additional schema in the output

USE tSQLt
GO
SELECT * FROM sys.schemas
WHERE schema_id BETWEEN 5 AND 1000

nameschema_idprincipal_id
tSQLt 51
WebFlags61

Let's create a couple of test stored procedures with the examples we used before

First we need to recreate the test1 table again

USE UnittestExamples
GO
CREATE TABLE dbo.test1(id int)
INSERT dbo.test1 VALUES(1)
INSERT dbo.test1 VALUES(3)
INSERT dbo.test1 VALUES(4)
GO

Now we will create 3 stored procedures. It is very important that you name each stored procedure beginning with 'test ', if you don't do this then tSQLt will skip the proc. Here is the code to create our 3 stored procedures

USE tSQLt
GO
CREATE PROCEDURE WebFlags.[test test1 table has all correct values]
AS
CREATE TABLE dbo.test2(id int)
INSERT dbo.test2 VALUES(2)
INSERT dbo.test2 VALUES(3)
INSERT dbo.test2 VALUES(5)
EXEC tSQLt.AssertEqualsTable 'UnittestExamples.dbo.test1', 'tSQLt.dbo.test2'

DROP TABLE dbo.test2
GO
CREATE PROCEDURE WebFlags.[test SomeTable table exists]
AS
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.SomeTable'
GO
CREATE PROCEDURE WebFlags.[test test1 table exists]
AS
EXEC tSQLt.AssertObjectExists 'UnittestExamples.dbo.test1'
GO

To execute these procs, all we need to do is run the following

tSQLt.tSQLt.Run 'WebFlags'

Here is the output

[WebFlags].[test SomeTable table exists] failed: (Failure) 'UnittestExamples.dbo.SomeTable' does not exist
[WebFlags].[test test1 table has all correct values] failed: (Failure) Unexpected/missing resultset rows!
|_m_|id|
+---+--+
|< |1 |
|> |4 |
|= |3 |
|> |5 |
|> |2 |

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name |Dur(ms)|Result |
+--+----------------------------------------------------+-------+-------+
|1 |[WebFlags].[test test1 table exists] | 3|Success|
|2 |[WebFlags].[test SomeTable table exists] | 6|Failure|
|3 |[WebFlags].[test test1 table has all correct values]| 33|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 3 test case(s) executed, 1 succeeded, 2 failed, 0 errored.
-----------------------------------------------------------------------------

As you can see, on top you will get the name of the tests that failed, also included is the error message

[WebFlags].[test SomeTable table exists] failed: (Failure) 'UnittestExamples.dbo.SomeTable' does not exist
[WebFlags].[test test1 table has all correct values] failed: (Failure) Unexpected/missing resultset rows!
|_m_|id|
+---+--+
|< |1 |
|> |4 |
|= |3 |
|> |5 |
|> |2 |

Below that you will get a list of each that that ran with an indication if the test was successful or not

|No|Test Case Name |Dur(ms)|Result |
+--+----------------------------------------------------+-------+-------+
|1 |[WebFlags].[test test1 table exists] | 3|Success|
|2 |[WebFlags].[test SomeTable table exists] | 6|Failure|
|3 |[WebFlags].[test test1 table has all correct values]| 33|Failure|

Finally you get a summary

Test Case Summary: 3 test case(s) executed, 1 succeeded, 2 failed, 0 errored.

You can also run all these tests by running the following

tSQLt.tSQLt.RunAll

Right now since you only have 1 test class, the result is the same if you run all tests or the tests for the WebFlags class

That's all for today, in the next post we will take a look at tSQLt available assertions and how they are used

The list of all the unit test posts can be found here: Unit testing in SQL Server, a list of all the posts

Posted by Denis at 4:22 PM No comments:

Email This BlogThis! Share to X Share to Facebook Share to Pinterest

Labels: tSQLt, unit testing, unit tests

Friday, October 02, 2015

Dropping all objects in a schema

I had the need to drop all objects that I created in a specific schema as part of a unit test. Then I decided to test my script on the Adventure Works 2014 sample database and I got a couple of errors. Some of these errors were that there were foreign key constraints from a different schema pointing to a table in this schema. I also got an error complaining about an XML SCHEMA collection living in my schema.

If you want to follow along by running the scripts, first download the Adventure Works 2014 sample database

After you are done downloading the database, create a folder named training on your C drive.
Open up SSMS, open a query window, run the following script

USE [master]
--DROP DATABASE AdventureWorks2014
GO
RESTORE DATABASE AdventureWorks2014
FROM disk= 'C:\Training\AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_data' TO 'C:\Training\AdventureWorks2014.mdf',
MOVE 'AdventureWorks2014_Log' TO 'C:\Training\AdventureWorks2014.ldf'
,REPLACE, stats =20

Now that the DB is available, we can look at what the script looks like. Connect to the database you just created and open up a new query window. Before running the script make sure that you are in text mode, just hit CTRL + T in your SSMS query window

Here is what the script looks like

 SET NOCOUNT ON
 DECLARE @SchemaName NVARCHAR(100) = 'HumanResources'
 SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id))
 +'.'+ QUOTENAME(OBJECT_NAME(s.parent_object_id))
 + ' DROP CONSTRAINT ' + QUOTENAME(s.name) 
 FROM sys.foreign_keys s
 JOIN sys.tables t on s.parent_object_id = t.object_id
 JOIN sys.tables t2 on s.referenced_object_id = t2.object_id
 WHERE t2.schema_id = SCHEMA_ID(@SchemaName)
 SELECT 'DROP ' +
 CASE WHEN type IN ('P','PC') THEN 'PROCEDURE'
 WHEN type = 'U' THEN 'TABLE'
 WHEN type IN ('IF','TF','FN') THEN 'FUNCTION'
 WHEN type = 'V' THEN 'VIEW'
 END +
 ' ' + QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name) 
 FROM sys.objects
 WHERE schema_id = SCHEMA_ID(@SchemaName)
 AND type IN('P','PC','U','IF','TF','FN','V')
 ORDER BY CASE WHEN type IN ('P','PC') THEN 4
 WHEN type = 'U' THEN 3
 WHEN type IN ('IF','TF','FN') THEN 1
 WHEN type = 'V' THEN 2
 END
 SELECT 'DROP XML SCHEMA COLLECTION '
 + QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name)
 FROM sys.xml_schema_collections
 WHERE schema_id = SCHEMA_ID(@SchemaName)
 SELECT 'DROP SCHEMA ' + QUOTENAME(@SchemaName)

As you can see, there are 4 things going on here.

Remove the constraints
Drop Views, Function, Stored Procedures and Tables, drop functions and views first, procs last
Drop XML Schema Collections
Drop the Schema

Of course if you have a view in another database or schema referencing your objects inside this schema you will have problems...I will get to this later

Running that script will give you the following output

ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] 
DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] 
DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID]
ALTER TABLE [Production].[Document] 
DROP CONSTRAINT [FK_Document_Employee_Owner]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] 
DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_BusinessEntityID]
ALTER TABLE [HumanResources].[EmployeePayHistory] 
DROP CONSTRAINT [FK_EmployeePayHistory_Employee_BusinessEntityID]
ALTER TABLE [HumanResources].[JobCandidate] 
DROP CONSTRAINT [FK_JobCandidate_Employee_BusinessEntityID]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] 
DROP CONSTRAINT [FK_PurchaseOrderHeader_Employee_EmployeeID]
ALTER TABLE [Sales].[SalesPerson] 
DROP CONSTRAINT [FK_SalesPerson_Employee_BusinessEntityID]
DROP VIEW [HumanResources].[vEmployee]
DROP VIEW [HumanResources].[vEmployeeDepartment]
DROP VIEW [HumanResources].[vEmployeeDepartmentHistory]
DROP VIEW [HumanResources].[vJobCandidate]
DROP VIEW [HumanResources].[vJobCandidateEmployment]
DROP VIEW [HumanResources].[vJobCandidateEducation]
DROP TABLE [HumanResources].[Shift]
DROP TABLE [HumanResources].[Department]
DROP TABLE [HumanResources].[Employee]
DROP TABLE [HumanResources].[EmployeeDepartmentHistory]
DROP TABLE [HumanResources].[EmployeePayHistory]
DROP TABLE [HumanResources].[JobCandidate]
DROP PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
DROP PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
DROP PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
DROP XML SCHEMA COLLECTION [HumanResources].[HRResumeSchemaCollection]
DROP SCHEMA [HumanResources]

Run that and it should all run fine, the schema should be gone

Now change @SchemaName NVARCHAR(100) ='HumanResources' to @SchemaName NVARCHAR(100) ='Sales'

Run the script again, run the output from the script

Finally change the variable to Person @SchemaName NVARCHAR(100) ='Person'

Run the script, run the output. All 3 schemas we specified should be gone now

Now restore the database again, This time run the code to remove the Sales Schema, Then run the code to remove the Person Schema.

Here is the error you will receive

Msg 6328, Level 16, State 1, Line 39
Specified collection 'AdditionalContactInfoSchemaCollection' cannot be dropped because it is used by object 'HumanResources.vEmployee'.

However, it is really used by the Person.Person table which we just dropped. In this case you will need to drop the view. If you have stuff like this in the database, you probably don't want to just start dropping objects in another schema that point to objects in your schema

I am not having these kind of issues so the code is good enough for me to wipe out objects that have been created as part of my unit testing

Posted by Denis at 7:37 AM 4 comments:

Email This BlogThis! Share to X Share to Facebook Share to Pinterest

Labels: DDL, maintenance, Schemas, sql server 2014, unit testing

Sunday, September 27, 2015

Unit testing in SQL Server, taking a first look a tSQLt

This is the third post in my Unit Testing series, today we are looking at what tSQLt, in the next post we will take a look at how to install tSQLt

What is tSQLt? Here is how it is described on the tSQLt website

tSQLt is a database unit testing framework for Microsoft SQL Server. tSQLt is compatible with SQL Server 2005 (service pack 2 required) and above on all editions.

Main features

tSQLt allows you to implement unit tests in T-SQL. This is important as you do not have to switch between various tools to create your code and your unit tests. tSQLt also provides the following features to make it easier to create and manage unit tests:

Tests are automatically run within transactions – this keeps tests independent and reduces any cleanup work you need
Tests can be grouped together within a schema – allowing you to organize your tests and use common setup methods
Output can be generated in plain text or XML – making it easier to integrate with a continuous integration tool
Provides the ability to fake tables and views, and to create stored procedure spies – allowing you to isolate the code which you are testing

So basically after you install tSQLt on your SQL Server instance, it is just a database, you now have a unit test framework that you can use. They way you use tSQLt is by creating stored procedures that will do the unit testing for you. It all runs within SQL Server.

You can run 1 test, all tests in a test class or all tests for all test classes by calling a single stored procedure. Here are the Assertions and Expectations that ship with tSQLt

Assertions

AssertEmptyTable

AssertEquals

AssertEqualsString

AssertEqualsTable

AssertLike

AssertNotEquals

AssertObjectDoesNotExist

AssertObjectExists

AssertResultSetsHaveSameMetaData

Fail

Expectations

ExpectException

ExpectNoException

In addition to that you can of course use IF EXISTS..... and then a Fail Assertions to test anything that is not testable with the built in Assertions

In addition to be able to run this all from a query window in SSMS, if you have Red Gates SQL Test then you can use that to run the tests, you will get a graphical indicator at that point. If the test passes it will be green, if it fails it will be red.

In the next post, we will take a look at how to install tSQLt on your database instance and write our first test.

Posted by Denis at 10:56 AM No comments:

Email This BlogThis! Share to X Share to Facebook Share to Pinterest

Labels: tSQLt, unit testing, unit tests

Wednesday, September 23, 2015

Unit testing in SQL Server..what are unit tests

This is a continuation from yesterday's post Unit testing in SQl Server... why? Today we will look at what unit tests actually are.

Test on an individual unit of code.
A unit test should ideally only test one discrete unit of code, there should not be a test for example how this unit of code reacts with other units of code. Tests should not be influenced by other units.

Repeatable
Executing a unit test should give you the same result every single time. Since a unit test is repeatable it is also easy to be automated.

Test one thing only
A test should only be testing one thing, it should test one question only. For example if I put money in the account did the balance increase?

Test the requirement for the unit.
If the requirement is that zip codes are 5 digits for example then you should have tests that verify that 5 is correct and for example 4 and 6 will fail the test.

When do I write unit tests?

Before development?
This enables you to focus on requirements, it is part of Agile, it also allows you to develop code structures first

During development?
If requirements change you need to create new unit tests or modify existing unit tests

After development?
You will need to add tests that validate existing code

The answer is of course during all 3 phases!
You will be writing unit tests when you change something in an existing application, start a new application or if you are in the middle of development for a soon to be launched application. The only difference is that with a new application you will write the test before the code and with an existing application that you will write the unit test after the code already exists.

Why are you not unit testing yet?
I asked this question and here are some of the answers I got...I hope the last one is a joke :-)

Don’t know where to start
No time
No benefit
Our customer do our unit testing for us

Hopefully once you are done reading all these posts, these answers should not prevent you from starting to do unit testing

Posted by Denis at 11:46 AM No comments:

Email This BlogThis! Share to X Share to Facebook Share to Pinterest

Labels: tSQLt, unit testing, unit tests

Unit testing in SQl Server... why?

I have been using tSQLt to do unit testing within SQL Server for the last 2 years. I already did a presentation twice at work and decided to document some of this so that it might help other people looking at using tSQLt to do unit testing with SQL Server.

This will be a multi part series, some of the stuff I will cover:

What is unit testing and why you should be unit testing
What tSQLt is and how to get started
Sample of all the different functionality that tSQLt provides
How to integrate tSQLt within your builds

Before starting, let me first tell you what happens when something is checked in into out source code repository.
1) The applications are build
2) Integration tests are run
3) Unit tests are run
4 If there were any errors an email is sent to the developers with the output of the failed tests or compilation errors

If there are errors, tSQLt will give you the name and the error for each test as well have a summary which looks something like the following

---------------------------------------------------------------------------------
Test Case Summary: 128 test case(s) executed, 127 succeeded, 1 failed, 0 errored.
---------------------------------------------------------------------------------

Before explaining what unit tests are, let take a look at what the cost is of fixing a bug

Take a look at this image below from a presentation by Stefan Priebsch, it shows you what the cost is of fixing a bug within the software lifecycle

As you can see, the relative cost fixing a bug is 150 times compared to if it was fixed during the requirements stage.

When doing unit testing, you hopefully will catch the errors before it gets to the point where the cost to fix the bug is 20x or higher.

In the next post I will explain what unit tests are and what they will provide to you and your team

Posted by Denis at 9:49 AM No comments:

Email This BlogThis! Share to X Share to Facebook Share to Pinterest

Labels: tSQLt, unit testing, unit tests