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


A T-SQL Code Testing Guide


A T-SQL Code Testing Guide

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)

Group: Administrators
Points: 620008 Visits: 21261
Comments posted to this topic are about the item A T-SQL Code Testing Guide

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Robert Sterbal-482516
Robert Sterbal-482516
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2442 Visits: 332
Is there an inflection point for the utility of running the tests versus how many times you will use the script?
Gary Varga
Gary Varga
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74428 Visits: 6562
Having done unit testing for 14 years using standard frameworks and about a further 5 years using handcrafted frameworks, I find it rather restricted, limited and backward that I do not apply the same techniques when coding for RDBMS'. This is in part due to the lack of tools (there are some out there as the article highlights), lack of support from the DBA teams (there will be exceptions, however, this is my experience) and/or lack of authority to add any SDLC tools, be they homespun or third party.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
ed.elliott
ed.elliott
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: 26
We, as T-SQL developers definitely do need to get better and formalize our testing, I blogged the other day about how to test constraints for example:

http://sqlserverfunctions.wordpress.com/2014/10/05/unit-testing-constraints-in-sql-server/

I believe unit testing basically comes down to separating your code into small units (i.e. Lets get away from thousand line stored procedures) and deciding what the code unit should and also inversely shouldn't do!
dean.giberson 64357
dean.giberson 64357
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 55
My idea of testing begins at the requirement. You need to have the test data in place and the input and output defined for each phase or unit of testing before you start coding. This allows you to test each section of code as you build. Once you have the code completed (you think) you can test against the expected results and for performance. It is critical to have enough data to test all scenarios of input and output and performance. I think it would be great to have some automated way to do this testing but I can't think of a way that wouldn't require almost as much work to set up as it does to just test as you go. With regard to testing code you are changing, it becomes even more important and difficult to test someone else's code since you may not have the full details of what it used to do only what you have been asked to make it do now which can lead to missing tests that should be done to make sure it still does what it needs to as well as what you are making it do. Testing is hard and I know I have not done an adequate job at times do to a lot of factors. Hopefully I am getting better with each assignment.
Gary Varga
Gary Varga
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74428 Visits: 6562
dean.giberson 64357 (10/7/2014)
...I think it would be great to have some automated way to do this testing but I can't think of a way that wouldn't require almost as much work to set up as it does to just test as you go...


Once people started using unit testing tools this very issue was resolved by the tools providers (amongst others). They "stole" ideas off of each other and drastically lowered the time it takes to write the tests. Also, tests being continuously run ensures that even after you leave your code your tests remain in place providing continuous value.

It just needs a push by a subsection of DBAs to drive this into common practice.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Iwas Bornready
Iwas Bornready
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67264 Visits: 886
I've yet to come across a way to test for everything. So no matter how much we test, and the more the better, there will always be that chance that something slips through, some error you just can't find until it goes to production.
Iwas Bornready
Iwas Bornready
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67264 Visits: 886
Fortunately for me, my mistakes won't cost someone their life.
Gary Varga
Gary Varga
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74428 Visits: 6562
crussell-931424 (10/7/2014)
I've yet to come across a way to test for everything. So no matter how much we test, and the more the better, there will always be that chance that something slips through, some error you just can't find until it goes to production.


There are techniques, tools and languages that can afford this but it requires a prohibitive amount of time. Prohibitive due to the distinct lack of life threatening risk as you say:

crussell-931424 (10/7/2014)
Fortunately for me, my mistakes won't cost someone their life.


(I am in a similar boat i.e. if I mess up no one dies - unless I believe the PM that they will kill me ;-))

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)

Group: Administrators
Points: 620008 Visits: 21261
Robert Sterbal-482516 (10/6/2014)
Is there an inflection point for the utility of running the tests versus how many times you will use the script?


Probably, but I'm not sure. I (personally) think the value is mostly in the number of times I may change something. I think these unit tests are most valuable for preventing regression issues as I evolve things than testing the happy path I expect.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
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