Is It Worth Writing Unit Tests?

  • Ed Elliott

    Ten Centuries

    Points: 1365

    Comments posted to this topic are about the item Is It Worth Writing Unit Tests?

  • David.Poole

    SSC Guru

    Points: 75109

    Well said!

    I'd question that you double the time taken because you actually think what you are doing. I think this adds discipline to the development process and actually saves time up front. I think the perspective of doubling comes from the visibility of the disciplined act where as without out it time spent faffing about trying to absorb or bolt on fixes goes up by multiples and is hidden as part of the development process.

    Something that I feel is overlooked is that although there is benefit up front from TDD/BDD the true benefits appear in subsequent projects using the coding artifacts. It is a pay it forward approach with a hefty deposit.

  • Ed Elliott

    Ten Centuries

    Points: 1365

    Thank you!

    I guess what I was trying to get across is that it often seems quicker to go and just fix something but it always ends up taking longer in the end whereas an early trade off of doubling the time or more to do a fix is always worth it.

    ed

  • JohnJack

    SSC Enthusiast

    Points: 163

    Unit Tests are often under rated. The true value of them is in being able to do quick regression testing on complex systems where stored procedures can produce differing results based on what data is being fed into them. To be able to run a full test with a single click is priceless.

    One problem however is where you run in an environment heavily reliant on developers intent on inflicting ORM's on enterprise level software as a short cut

  • ben.pittoors

    Old Hand

    Points: 343

    The real question is "Should you write 'terminate employee' business logic in a stored procedure?"

    You don't have to convince me on the ROI of Unit Tests, they're a proven methodology to get robust, maintainable and readable source code that does as the developer think it has to do. But seriously, I know we all love T-SQL (I do too), but why would you write this kind of business logic at the db level?

    (T-)SQL shines in set based operations though... but they're very cumbersome to test.

  • Ed Elliott

    Ten Centuries

    Points: 1365

    I agree that business logic doesn't really belong in the database, thousand line stored procedures should have the logic moved up into the application.

    There are cases though where you need to have some logic in stored procedures, if you need to do something like aggregate totals on tables with billions of rows then returning the data to an application would be really slow and sometimes you need some logic close to the data.

  • peter-757102

    SSCertifiable

    Points: 6877

    I do like to spend time to design better early on and do various tests to confirm i am on the right track.

    And doing so by making mini-scripts and specific test scenario's with sparse commenting.

    Once I know the design and solution works, it works and that concludes the lifespan of the tests.

    When something changes that affects implementation, it also affects design and design decisions...making formal tests obsolete in most non-trival situations.

    While I understand the safety feeling tests can bring, I also wonder why few recognize that the person writing the tests is the same person that does the implementation and often even the overall modelling and design.

    This means tests are an extra layer of code that can go wrong, while not really challenging the design properly.

    Flaws in thinking about the design, in the implementation or in coding the tests are essentially the same.

    Tests can only prove that ** when ** the developer works precise, the implementation does what the developer thinks it should do (which changes as soon the system gets modified).

    In my eyes working precise and being assertive is what counts.

    The idea that creating formal tests as a way of working, somehow magically brings that...I do not see that with myself, nor do I see it with others.

    The amount of 'code' in a database should be pretty limited, most of the guarantees should be inherent in the modelling (normalization and constraints).

  • Ed Elliott

    Ten Centuries

    Points: 1365

    Hi Peter,

    I guess the point is that if a change is made you can see what tests fail and either fix or remove the tests. You can of course do this work methodically but there is a possibility that you will miss something and also mistakes happen, unit test should catch those.

    When you have a full suite of unit tests, any change you make means you know exactly what functionality has changed.

    Changing functionality often breaks tests, great - a decision can be then made what to do based on facts.

    ed

  • ben.pittoors

    Old Hand

    Points: 343

    peter-757102 (7/23/2015)


    I do like to spend time to design better early on and do various tests to confirm i am on the right track.

    And doing so by making mini-scripts and specific test scenario's with sparse commenting.

    Once I know the design and solution works, it works and that concludes the lifespan of the tests.

    When something changes that affects implementation, it also affects design and design decisions...making formal tests obsolete in most non-trival situations.

    While I understand the safety feeling tests can bring, I also wonder why few recognize that the person writing the tests is the same person that does the implementation and often even the overall modelling and design.

    This means tests are an extra layer of code that can go wrong, while not really challenging the design properly.

    Flaws in thinking about the design, in the implementation or in coding the tests are essentially the same.

    Tests can only prove that ** when ** the developer works precise, the implementation does what the developer thinks it should do (which changes as soon the system gets modified).

    In my eyes working precise and being assertive is what counts.

    The idea that creating formal tests as a way of working, somehow magically brings that...I do not see that with myself, nor do I see it with others.

    The amount of 'code' in a database should be pretty limited, most of the guarantees should be inherent in the modelling (normalization and constraints).

    Code that is testable, regardless of the quality of the tests, already leads to better design. And don't get me started on 'design decisions'... if you make any design decision that puts a constraint on your source code to NOT be unit tested then that is a BAD design decision. There are exceptions (hw constraints in embedded devices for example) but in general your code should be testable and unit tested. It is just a method to reduce and control technical debt, there's no magic involved.

  • John Mitchell-245523

    SSC Guru

    Points: 148259

    peter-757102 (7/23/2015)


    Tests can only prove that ** when ** the developer works precise, the implementation does what the developer thinks it should do (which changes as soon the system gets modified).

    Yes, that's all we want it to do. It's a sanity check to make sure that what you've written works, and continues to work when other changes are made. It's not user acceptance testing or integration testing or anything like that. It's got to be better to be able to detect bugs yourself than have UAT testers, for example, tell you about them. Sure, you won't catch everything, but each bug that you find and fix enhances (or limits the damage to) your reputation, and leaves you free to get on with something else.

    John

  • peter-757102

    SSCertifiable

    Points: 6877

    John Mitchell-245523 (7/23/2015)


    peter-757102 (7/23/2015)


    Tests can only prove that ** when ** the developer works precise, the implementation does what the developer thinks it should do (which changes as soon the system gets modified).

    Yes, that's all we want it to do. It's a sanity check to make sure that what you've written works, and continues to work when other changes are made. It's not user acceptance testing or integration testing or anything like that. It's got to be better to be able to detect bugs yourself than have UAT testers, for example, tell you about them. Sure, you won't catch everything, but each bug that you find and fix enhances (or limits the damage to) your reputation, and leaves you free to get on with something else.

    John

    What you describe now is not unit testing, but testing what you write, for yourself (as I like to do).

    Writing proper repeatable tests for use by others is much harder then testing your own code to see if it works (and orders of magnitude more time consuming).

  • Ed Elliott

    Ten Centuries

    Points: 1365

    Have you looked at tSQLt? It really makes writing unit tests a lot simpler and easier.

    I talk about why it is good and makes it easier to write tests (https://the.agilesql.club/Blog/Ed-Elliott/AdventureWorksCI-Step5-Adding-tSQLt-Dacpac-To-The-Solution)

  • John Mitchell-245523

    SSC Guru

    Points: 148259

    peter-757102 (7/23/2015)


    John Mitchell-245523 (7/23/2015)


    peter-757102 (7/23/2015)


    Tests can only prove that ** when ** the developer works precise, the implementation does what the developer thinks it should do (which changes as soon the system gets modified).

    Yes, that's all we want it to do. It's a sanity check to make sure that what you've written works, and continues to work when other changes are made. It's not user acceptance testing or integration testing or anything like that. It's got to be better to be able to detect bugs yourself than have UAT testers, for example, tell you about them. Sure, you won't catch everything, but each bug that you find and fix enhances (or limits the damage to) your reputation, and leaves you free to get on with something else.

    John

    What you describe now is not unit testing, but testing what you write, for yourself (as I like to do).

    Writing proper repeatable tests for use by others is much harder then testing your own code to see if it works (and orders of magnitude more time consuming).

    No, I'm still talking about unit testing. Like Ed says, use tSQLt. Yes, it's harder than doing a simple select statement, but it's more rigorous, it's reusable, and it means fewer bugs go forward to the next stage of testing, so I'd argue that it saves time in the long term.

    John

  • peter-757102

    SSCertifiable

    Points: 6877

    ben.pittoors (7/23/2015)


    peter-757102 (7/23/2015)


    ...

    Code that is testable, regardless of the quality of the tests, already leads to better design. And don't get me started on 'design decisions'... if you make any design decision that puts a constraint on your source code to NOT be unit tested then that is a BAD design decision. There are exceptions (hw constraints in embedded devices for example) but in general your code should be testable and unit tested. It is just a method to reduce and control technical debt, there's no magic involved.

    The forum ate my long reply as to why you are wrong in these claims.

    Forgot to copy to clipboard first and the before session timed out....(please admin, fix this forum issue...it needs it).

    What it came down to is this (very distilled version).

    I disagree and see unit testing put constraints on designs as a sign that unit test implementations are not well developed at all (not SQL Server specific)! It is a rogue idea that gotten out of hand and often requires many clumsy tools to be used, in a non-productive matter, even for trivial tests.

    Structurally as a habit relying on tests to provide code quality will only end up in less-strong/sloppy coders (they are only human after all).

    Take the unit tests away from someone used to it and most likely they worse as a result of this earlier dependence (at least for a while).

    But those that did not follow the same learning path will deliver better code and will just find them being in the way of productivity.

    Proper unit testing is also very asymmetrical, it costs orders of magnitude more time and mental power then it saves if applied broadly as a way of "doing things". That is not to say it is never the right thing to do, but do so with ** extreme ** moderation!

    SQL Server not being a procedural environment that works only optimal with "hard to automatically test" sets makes unit testing harder then in other languages. As religiously spread unit testing is in education, to the level of 100% code coverage been seen as ideal, I will contest it's use as I seen the damage broad application of it does. Even in languages as C#, where the procedural nature and data types align better, extremely clumsy tools and some silly design restrictions are needed.

  • peter-757102

    SSCertifiable

    Points: 6877

    Ed Elliott (7/23/2015)


    Have you looked at tSQLt? It really makes writing unit tests a lot simpler and easier.

    I talk about why it is good and makes it easier to write tests (https://the.agilesql.club/Blog/Ed-Elliott/AdventureWorksCI-Step5-Adding-tSQLt-Dacpac-To-The-Solution)

    I will take a look for sure when I get home!

    There are always a few cases that justify repeatable tests in my book.

Viewing 15 posts - 1 through 15 (of 87 total)

You must be logged in to reply to this topic. Login to reply