Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SQL Unit Testing Expand / Collapse
Author
Message
Posted Thursday, April 9, 2009 4:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 449, Visits: 1,864
RichardB (4/9/2009)
GermanDBA (4/8/2009)

you could either backup the database, run the test and then restore the backup or take a snapshot of the database and restore that after the unit test.


Only issue being that it's a 1.5TB database... so around a day to restore.


Ahaa.... two solutions for that.

1. Either use the Database Snapshot and restore from that: really fast, because only the changes are stored in a sparse file so these would be 'rolled back' instead of restoring the entire 1.5TB
A quick explanation with example can be found here : http://blogs.technet.com/mscom/archive/2007/08/08/using-sql-2005-snapshots-as-a-rollback-procedure.aspx, otherwise BOL / Google.

2. Use SQLBackup from Redgate, we use it and it is screaming fast compared to native backup and restore, with the added benefit of compressing the backups to save disk space.


@1 - Database snapshots are for Enterprise Edition only. However, we are talking about unit tests, so you can do your unit tests on SQL Server Developer Edition (same feature set as Enterprise, but for development use only and available for $50 or so IIRC).

regards

GermanDBA



Regards,

WilliamD
Post #693845
Posted Tuesday, April 14, 2009 4:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:06 AM
Points: 424, Visits: 237
I'm not sure if this is really unit testing - it's more system testing. My understanding of unit testing is that you are looking at a specific routine (or maybe stored procedure) and ensuring that the results of that routine are as expected. Unless you spend time creating check SQL to ensure that the call to the SP has done what you expect, then all you are doing is syntax checking in case of database schema changes, and maybe performance testing.

However, it is very useful to do a full system test, so I can see big advantages in this in that you need only run through a system test once (the first time and whenever there are logic flow changes) and then re-run it in the future as part of a pre-deployment test, saving time and effort.
Post #696404
Posted Tuesday, April 14, 2009 6:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:03 PM
Points: 1,109, Visits: 556
Hi Brian,

This is a good observation, and I agree with you about the system testing if you just run it out-of-the-box.
This tool saves tons of time writing the queries from a trace file, but for deep unit testing you must use Asserts and Try/Catch in the C# code. By doing this, you can test the SPs passing valid/invalid parameters and validate your error handling and output. Then you'll be using unit tests and system tests all together.
Of course, you can use it just the way the tool writes the C# file and you'll have a bunch of test to your database just to validate that nobody broke the code. Also you can combine this with other unit testing tools (web unit testing, C# tests, etc) and create a robust unit testing environment.

Cheers,
Alejandro


Alejandro Pelc
Post #696463
Posted Tuesday, April 14, 2009 7:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:06 AM
Points: 424, Visits: 237
Alejandro,

I agree. The biggest problem with deep unit testing SPs is testing the results - it's not good enough to just check the output from the SP, you have to check that the SP has altered the data as you expect it. If your SPs are simple, then this is fairly easy, and obviously if all they are doing is returning some data then that is also easy. However, if there is some complexity involved, say for instance some involved transaction recording, then you may end up writing "unit testing" SPs to verify the original ones.

I guess that's another article altogether.
Post #696511
Posted Tuesday, April 14, 2009 8:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:03 PM
Points: 1,109, Visits: 556
Hey Brian,
totally agree about the complexity. I guess the first thing to do before using this is knowing how deep you want / can go. For brand new projects is easier, but if you want to implement it on an existing, complex one, then it'll be hard.
I think one approach for existing projects is configuring the basic test, just testing that SPs won't crash, and then start digging into the data. But as you said, this can be a new article itself...

cheers,
Alejandro


Alejandro Pelc
Post #696587
Posted Tuesday, April 14, 2009 1:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 5, 2011 2:44 PM
Points: 24, Visits: 22
This is interesting - but it's not generating unit tests - it's generating integration tests which are not diagnostic, and do not run quickly, or cover all functionality.

Post #696966
Posted Tuesday, April 14, 2009 2:33 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:03 PM
Points: 1,109, Visits: 556
Hi Jonathan,

I'm confused about what you mean with integration tests. Why you think this is the case ? It's true that I used it to test different systems, as mentioned in the article, but the hole idea is to test SQL SPs. I'm not making any unit test on reports, etc.
Perhaps I'm missing something, but I think the tool is about unit testing and depending the way you use it, you'll have good unit tests or just simple tests.

Thanks,
Alejandro


Alejandro Pelc
Post #697039
Posted Tuesday, April 14, 2009 2:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 5, 2011 2:44 PM
Points: 24, Visits: 22
Unit tests are specific in nature. A unit test determines whether a unit of code meets a certain specification. When unit tests fail they should point to a single line of code that is improperly written. Typically each unit of code will have mutiple unit tests to do proper testing.

What you are doing is running a load generated by Profiler against a database. You are running integration tests (or smoke tests) in this case as this test is not specific in nature, does not point to a single line of code when it fails, etc.

I am primarily a C# / ASP.NET / Ruby developer. If you want to do true unit tests for SQL I suggest you read some Ambler. He's good in this area.
Post #697045
Posted Tuesday, April 14, 2009 5:02 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:03 PM
Points: 1,109, Visits: 556
Hi Jonathan,

I see your point, I misunderstood the "integration test" line. Actually, it's a good observation, and I partially agree with you. If you just run the test as if, then it's more like a smoke test than a unit test. But you must consider that SQL is different that C# or .NET (I'm far away of being an expert in those areas) on the flexibility that unit test provides.

If you have a stored procedure that fails, and you execute it in Management Studio or other SQL client, you'll get the error and the line that fails. To have the same functionality on the unit tests, you must catch the error on the C# code because if not you'll only get the line that errored in the unit test, not the one in the SP. Using the try catch will give the test the exact line in the SP that failed, even if your SP built a dynamic query.

I'm sure that there are some other tools down there that do the same or even more than this, and I'll see if I can get my hands in some of them and make a comparison. By the way, thanks for the tip. I'll try to read some Ambler.

Thanks,
Alejandro


Alejandro Pelc
Post #697143
Posted Tuesday, April 21, 2009 8:58 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 8:38 AM
Points: 1,070, Visits: 908
WilliamD (4/9/2009)

1. Either use the Database Snapshot and restore from that: really fast, because only the changes
...
2. Use SQLBackup from Redgate, we use it and it is screaming fast compared to native backup and


Aye - once we get off 2000... we use Quest at the mo - still an 11 hour restore on our slow dev boxes!



Post #701543
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse