Testing Simple Calculations and UDFs with tSQLt

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

  • ScottPletcher

    SSC Guru

    Points: 98045

    Interesting points. (Although I'd prefer to put the test conditions / "known case" conditions into a table, for the usual reasons of flexibility and maintainability.)

    As an aside, the function code itself could be streamlined. I prefer to use RTRIM as a trailing space doesn't indicate another word following. Thus, I'd make the test data ' STRING' rather than 'STRING '.

    ALTER function [dbo].[calculateEstimateOfReadingTime]

    (

    @value varchar(max)

    )

    RETURNS int

    AS

    BEGIN

    RETURN (

    SELECT (LEN(@value) - LEN(REPLACE(RTRIM(@value), ' ', '')) + 1) / 250

    )

    END

    Edit: Added missing " / 250" to calculation.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    I'm not sure I'd want the test conditions in a table, but that could work. Potentially managing lots of tests is cumbersome, though I haven't done enough large scale work to have issues here.

    Thanks for the function replacement, but your function lacks the math part to divide by 250 and get the proper calculation. A good reason to have testing ready!

  • Jeff Moden

    SSC Guru

    Points: 994239

    Great article. I'm just not sure why anyone needs to use tSQLt for such a thing. It seems like an extra layer that's not needed. There's also the fact that once a UDF is written, tested, and promoted to production, it almost never needs to be updated.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    I'm not sure I agree UDFs will never be updated. If not, I totally agree. Test once, leave alone.

    I want testing for anything that will be refactored.

  • Jeff Moden

    SSC Guru

    Points: 994239

    I did say that most UDFs would never be updated, not all but, let's ask the question.

    How many UDFs that were already in production have you actually updated?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • ScottPletcher

    SSC Guru

    Points: 98045

    Presumably some number of UDFs would get updated over time, particularly as new techniques are learned. I believe, for example, that the splitter function has gone thru several iterations, as has the replace-multiple-spaces-with-a-single-space function. Also, it's possible a new value or possibility in the data might require a function change.

    To me one of the big benefits of a testing set is that it can contain all the special cases that need to be checked. We've all seen cases where a rare input causes long-time code to suddenly produce a buggy result.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • Jeff Moden

    SSC Guru

    Points: 994239

    I agree but why does one need the extra layer of tSqLt? DelimitedSplit8k even has two different tests built into the header so they won't get lost.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    Jeff Moden (5/18/2015)


    I did say that most UDFs would never be updated, not all but, let's ask the question.

    How many UDFs that were already in production have you actually updated?

    A minority, but certainly some. However this applies to stored procedures as well. I chose a function to show that, but a proc could certainly be tested here.

    I do see these evolve over time, short or long. Short, especially when they are doing something that is business related, and is subject to change. I've seen far, far too many people give me "rules" for automating something they do manually, only to realize that they have exceptions that come into play for their rules, but which are rare. Once they realize they've forgotten an exception, we need to handle it.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    Jeff Moden (5/19/2015)


    I agree but why does one need the extra layer of tSqLt? DelimitedSplit8k even has two different tests built into the header so they won't get lost.

    Automation. Ensuring the tests are run each time the solution is updated.

  • Jeff Moden

    SSC Guru

    Points: 994239

    Steve Jones - SSC Editor (5/19/2015)


    Jeff Moden (5/18/2015)


    I did say that most UDFs would never be updated, not all but, let's ask the question.

    How many UDFs that were already in production have you actually updated?

    A minority, but certainly some. However this applies to stored procedures as well. I chose a function to show that, but a proc could certainly be tested here.

    I do see these evolve over time, short or long. Short, especially when they are doing something that is business related, and is subject to change. I've seen far, far too many people give me "rules" for automating something they do manually, only to realize that they have exceptions that come into play for their rules, but which are rare. Once they realize they've forgotten an exception, we need to handle it.

    I wonder if 2014 SP1 and the "retro-accidents" that occurred in 2012 first made it to the street using such automation. 😀

    To be honest, it would take MUCH more time to write the automation to do the tests than it would take to design the procs and related tables from the ground floor including the education of the project managers, QA, and the users. I'd also love to see what the automation would look like for the batch file processing we do.

    Automation might be fine for UDFs, report procs, and simple crud but, unless someone takes the time to make a gold set and a total reset, I believe that automation would be mostly out of the question for what we do. Instead, we use run-time output of internal validation in the procs to "test" the procs. It's necessary anyway because we also have to produce auditable run time logs.

    It also guarantees that a human looks at it instead of just a machine supposedly trained by humans. As they say "Humans makes mistakes but, if you really want to screw something up quickly, it takes a computer". 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 11 (of 11 total)

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