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


SQL Unit Testing


SQL Unit Testing

Author
Message
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: 1183 Visits: 584
Comments posted to this topic are about the item SQL Unit Testing

Alejandro Pelc
msbi.afaik
msbi.afaik
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 26
is it possible to test ssrs reports?

Regards,
KarthikShanth.
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: 1561 Visits: 1058
sounds interesting.

The main issues I guess would be around identities/autoincrements and anything with password in the call!

Do you have some way of resetting the database after?



w.durkin@online.de
w.durkin@online.de
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 1879
RichardB (4/8/2009)
sounds interesting.

The main issues I guess would be around identities/autoincrements and anything with password in the call!

Do you have some way of resetting the database after?


Hi RichardB,

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.

This would guarantee a database being in the same state for each unit test run.

Regards

GermanDBA

Regards,

WilliamD
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: 1183 Visits: 584
Hi there,
GermanDBA is right, you should create some sort of backup/restore strategy or include the DB creation / data population prior to run the tests and then just drop the database

Alejandro Pelc
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: 1183 Visits: 584
Hi karthikshanth,
You can actually test the calls to the database, so in a certain way you are testing the SSRS backend.
If you want to test the reports, there's a free tool called WatIN (http://watin.sourceforge.net) you can use to do that. It uses the IE and records all the activity in the page. It has its limitations but you can make it work.

Thanks,
Alejandro

Alejandro Pelc
Andrew Peterson
Andrew Peterson
Say Hey Kid
Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)

Group: General Forum Members
Points: 682 Visits: 725
This sounds like it could have many uses. Thanks for the contribution.

The more you are prepared, the less you need it.
Mike Byrd
Mike Byrd
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 389
Interesting article and very timely for me. My big question is how do you detect errors/slow running queries etc? Do you need to run profiler again to detect database problems? Is there any means to detect when the newly revised code doesn't error, but is inserting/updating incorrect data? When you have a 150gb OLTP database, it is difficult to determine problems down at the row/column level. Any ideas on this?

Cheers,
Mike Byrd

Mike Byrd
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: 1183 Visits: 584
Hi Mike,

The test will validate if it runs without errors. Because you're executing stored procedures or t-sql commands, if there's an error, the unit test will fail, so the overall test will fail (it'll show you, for instance, 10 test ok and 1 failed). Also, when you configure the test in a test solution in visual studio, you can configure the maximum execution time, and if it takes more than that time, the test will fail.
About the data, the whole idea of unit testing is testing all the different aspects of your database, including sending bad data just to validate your error handling. Of course, you must customize the output to do that, inserting some c# code...
Finally, I don't know if I would run unit tests on a 150 GB database just to validate the changes. I'll do that to test performance, but not for reviewing that changes didn't break existing procedures. Of course, every scenario is different...

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: 1561 Visits: 1058
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



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