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

Why Don't You Unit Test Code?

By Steve Jones,

First, I'll apologize to those that do use formal, automated tests in advance. I'm not really writing for you, and I'm sure you can teach me more about testing. Perhaps you can comment about how and why you do this, or even write some articles to help others.

For everyone else, why don't you test in a repeatable fashion? Let me know with a comment today.

One thing I've noticed as I've talked DevOps and testing for a number of years is that so many developers agree testing is a good idea, but claim they have no time, no support from management, or no need to repeat tests once code is written. Sometimes I hear all three excuses at the same time. The same people will also admit that they have problems with code at times, and that quality could be improved.

Perhaps it's me, but I've never had my boss sit there and watch me code. He or she doesn't know how I might write code. I certainly write a query, run it, and look at a bunch of rows and see if I think the code is correct. Perhaps I need to run other queries to verify values in the results. However, I could also build a quick test with something like tSQLt, like the one I set up for checking COALESCE that does the same thing. With my unit test, I can run it over and over, modifying the test as I discover new edge cases to validate. Which way do you want to write code?

Even if I have a set of data I need to assemble, it doesn't take much longer for me to write a test, especially when I need to also solve the problem. I find that putting a test together forces me to slow down and think about the rules for my code. Since I need to come up with some result set, building that into a test of some sort is fairly easy and let's me double check how I think data will change. I can then repeat the test over and over as I modify code. I can even grab sample data from production and use that as the basis for my unit test if I need to check a specific entity's values.

I know many things I write in SQL might not be worth a test. Often the simple CRUD queries and basic aggregations aren't things I'd test, but when my query becomes complex, includes APPLY or outer joins, and logic to decide how to filter or format data, wouldn't a unit test make sense? What about if you want to be sure you're processing the correct rows from a large CUBE or ROLLUP operator? Certainly if I'm fixing a bug, writing tests makes sense, at least I think so.

Let me know what you think today.

Total article views: 59 | Views in the last 30 days: 1
Related Articles

How implicit data type conversion slows down your query

Implicit data type conversion is one of those things you hardly think about or notice when writing y...


Thinking Time

Taking time away from life and just thinking is important.


sql query need Help about Absent Employee

Query about absent employee


Help writing query logic

Help writing query logic


10 Good Things About Reporting Services 2008 R2

The 10 things Microsoft got right about SQL Server Reporting Services 2008 R2 (a sister article show...

unit testing