SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Unit Testing


SQL Unit Testing

Author
Message
w.durkin@online.de
w.durkin@online.de
Say Hey Kid
Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)

Group: General Forum Members
Points: 667 Visits: 1879
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. w00t


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
Brian Jones-401554
Brian Jones-401554
SSC-Addicted
SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)

Group: General Forum Members
Points: 476 Visits: 292
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.
Ale Pelc
Ale Pelc
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 584
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
Brian Jones-401554
Brian Jones-401554
SSC-Addicted
SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)SSC-Addicted (476 reputation)

Group: General Forum Members
Points: 476 Visits: 292
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. ;-)
Ale Pelc
Ale Pelc
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 584
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
Jonathan Starr-211380
Jonathan Starr-211380
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
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.
Ale Pelc
Ale Pelc
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 584
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
Jonathan Starr-211380
Jonathan Starr-211380
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
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.
Ale Pelc
Ale Pelc
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1193 Visits: 584
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
RichB
RichB
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1633 Visits: 1058
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!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search