July 24, 2016 at 6:38 am
Comments posted to this topic are about the item Where's the Unit Testing?
July 24, 2016 at 7:40 am
I do more manual unit testing of each piece of code I write in SQL Server. I haven't really invested in what tools are available to make this easier, but I do test each unit befoe committing. I did however read some articles from Microsoft on 2012 unit tests, but they looked very complex and for 2012. I'm on 2008 R2 still.
I was thinking about porting the unittest module from Python to SQL to see if it could be benefitial. Here is a core example of how easy it is to test some string methods.
Reading over the code, assertEqual is called to test for an expected result, assertTrue or assertFalse to test for conditions and assertRaises to test for specific exceptions. All 3 tests are ran with the results returned to the end user.
import unittest
class TestStringMethods(unittest.TestCase):
def test_upper(self):
self.assertEqual('foo'.upper(), 'FOO')
def test_isupper(self):
self.assertTrue('FOO'.isupper())
self.assertFalse('Foo'.isupper())
def test_split(self):
s = 'hello world'
self.assertEqual(s.split(), ['hello', 'world'])
# check that s.split fails when the separator is not a string
with self.assertRaises(TypeError):
s.split(2)
if __name__ == '__main__':
unittest.main()
July 25, 2016 at 5:30 am
I use SSDT in visual studio for database projects and I use the built in test tooling for automated unit testing.
It has a nice SQL syntax wrapper over the tests.
Writing the automated tests are straight forward. Generating your base test data takes longer.
July 25, 2016 at 8:11 am
Indirectly asked a similar question a few years ago in http://www.simple-talk.com/dotnet/.net-framework/unit-testing-myths-and-practices/. And while the responses were interesting and thoughtful, most failed to answer the question you and Ben Taylor ask again today.
Suspect the primary reason remains unchanged. Most developers neither write nor enterprises demand unit tests without a compelling justification.
Maybe we need to ask the question, 'What justifies unit tests in practice?' And I’d recommend avoiding answers of the 'because you should' ilk because they clearly haven’t changed the status quo.
July 25, 2016 at 8:47 am
It depends on the organization and the project, but one barrier to unit testing is that many database development teams simply develope or deploy in discrete units. SQL coding has been replaced with things like Entity Framework, Master Data Services, where the SQL is abstracted away. The solution has to be regression tested, and developers usually don't have the skillset, time, or inclination for regression testing, because that's the job of QA.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 25, 2016 at 8:57 am
Since most of my SQL coding is set-based (1 query operation per procedure), unit testing seems pointless since there is only one unit. I do use SQLTest for some things, such as data integrity and data comparisons, but most of my code can't be broken up into units.
Tony
------------------------------------
Are you suggesting coconuts migrate?
July 25, 2016 at 9:15 am
When I'm writing a new SQL query, I'll mockup 10x the amount of data currently in production for the subset of tables that are relevent to query, so I can confirm that, in addition to producing an accurate result, it will also scale over time. Also, when I refactor an existing production stored procedure, something like a non-functional performance optimization, I will confirm that the same input parameters produce an identical output resultset. I don't want my performace "fix" to break anything funtionally. These are forms of unit testing.
Sometimes I'll even deploy the new stored procedure to production under a different name, so I can test both the original and new version side by side in identical environmental conditions, measuring not only duration but also CPU but I/O. I know what you're thinking, but yes, I sometimes do test in production, but only after it's been unit tested in development and QA, and you must know what you're doing to keep it isolated. When attempting to resolve a high profile issue, I don't want to mark a ticket as resolved only to discover post-production that it doesn't work the same in production.
Winners make their own luck. 😉
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 25, 2016 at 9:19 am
“Trying to improve software quality by increasing the amount of testing is like trying to lose weight by weighing yourself more often. What you eat before you step onto the scale determines how much you will weigh, and the software-development techniques you use determine how many errors testing will find.”
? Steve McConnell, Code Complete
More Unit Testing isn't the answer, although it may catch some of the more egregious errors. I blame development managers that have adopted Agile and used that as an excuse to drop design/code/test reviews.
Anyone who remembers SEI (Software Engineering Institute) will remember that the incorporation of reviews in Level 3 is where you got real gains in productivity and quality. With Design Reviews and Code Reviews (and Test Reviews), you not only catch obvious bugs, but you often uncover misunderstandings that can lead to more deep-seated problems - ones that don't show up until you are in production.
Active reviews, where the developer actively presents their designs and code to one or more reviewers, foster good techniques throughout the organization as well as catching problems early in the development cycle. New developers may not realize that there are excellent existing algorithms or other techniques that are simpler and more robust than their design approaches. Plus, just the act of "presenting" the design/code approaches often results in the developer realizing they missed something, or had intended to handle some situation, but somehow forgot/overlooked it.
Agile doesn't forbid the use of reviews, but too many proponents seem to think that reviews aren't necessary because of the short "sprint" methodology. And thus, I get handed systems that "passed testing", but fail or perform inefficiently in the real world, where if any experienced developer had looked at the coding, they would have recommended simpler and better approaches.
July 25, 2016 at 10:49 am
Where I work, the developers do a lot of test driven development for their C# work, but a limiting factor for them doing more on the database side is the tools available. They evaluated some methods, and tried tSQLt for a while, but had more problems with it than problems that it solved.
July 25, 2016 at 11:18 am
I tend to find that if something can't be unit tested easily and effectively it's because of some fundamental design problem. Usually i see this when a developer tries to make one thing that does everything and then ends up wondering why the thing they spent months building and just ran for the first time breaks.
July 25, 2016 at 11:50 am
From my observations, this is a management issue. And as you stated:
"...perhaps a few bugs aren't a problem. Maybe the impact is low enough that training developers to write tests and making the investment isn't valuable enough."
Like continuous integration, devops, etc. a few want a solid product, the many will wait to fix the bugs after the fact. I guess it makes it easy to prioritize which bugs to fix?
The more you are prepared, the less you need it.
July 25, 2016 at 1:00 pm
xsevensinzx (7/24/2016)
I was thinking about porting the unittest module from Python to SQL to see if it could be benefitial. Here is a core example of how easy it is to test some string methods.
You can port, but you might look at tSQLt before you do. It's similar, based on junit/nunit, with the specific changes for data sets.
July 25, 2016 at 1:02 pm
Tom Fischer (7/25/2016)
Maybe we need to ask the question, 'What justifies unit tests in practice?' And I’d recommend avoiding answers of the 'because you should' ilk because they clearly haven’t changed the status quo.
Avoiding regressions, formalizing (very slightly and lightweight), the work you should already do as a developer to ensure your code works before commit, and ensuring the tests can be a) automated and easily-re-run, and b) helping others to ensure they don't break your code when it is modified.
IMHO, this is like OOP development. A bit more work upfront, but as you get going, many changes become easier, and more reliable because you have documentation through tests on how code needs to perform.
July 25, 2016 at 1:03 pm
tabinsc (7/25/2016)
Since most of my SQL coding is set-based (1 query operation per procedure), unit testing seems pointless since there is only one unit. I do use SQLTest for some things, such as data integrity and data comparisons, but most of my code can't be broken up into units.
Parameters? Then you have edge cases. NULLs,0s/strange dates/etc. can occur in a single query, so having a few tests to ensure your code works well help when you might need to modify, or tune, the query.
July 25, 2016 at 1:04 pm
Eric M Russell (7/25/2016)
When I'm writing a new SQL query, I'll mockup 10x the amount of data currently in production for the subset of tables that are relevent to query, so I can confirm that, in addition to producing an accurate result, it will also scale over time. Also, when I refactor an existing production stored procedure, something like a non-functional performance optimization, I will confirm that the same input parameters produce an identical output resultset. I don't want my performace "fix" to break anything funtionally. These are forms of unit testing.Sometimes I'll even deploy the new stored procedure to production under a different name, so I can test both the original and new version side by side in identical environmental conditions, measuring not only duration but also CPU but I/O. I know what you're thinking, but yes, I sometimes do test in production, but only after it's been unit tested in development and QA, and you must know what you're doing to keep it isolated. When attempting to resolve a high profile issue, I don't want to mark a ticket as resolved only to discover post-production that it doesn't work the same in production.
Winners make their own luck. 😉
Those are great ideas. Unit testing should help here, with ensuring that your tests are captured and repeatable.
I like the 10x thing, but that's not possible in some cases. Ever have a 100GB table? Moving to a 1TB table isn't necessarily easy. However, I admire this. My vote is that at some point, at least in a load test, you always go to 1.2-2x production data sets.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply