Testing Simple Calculations and UDFs with tSQLt

Question

Testing Simple Calculations and UDFs with tSQLt

Steve Jones - SSC Editor

SSC Guru

Points: 736173
More actions
May 16, 2015 at 9:57 pm

#300781

Comments posted to this topic are about the item Testing Simple Calculations and UDFs with tSQLt

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

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

ScottPletcher SSC Guru Points: 101096 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 2

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: 1004323 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

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

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 4

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: 1004323 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

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

ScottPletcher SSC Guru Points: 101096 More actions · Answer 6

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

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

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 8

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: 736173 More actions · Answer 9

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: 1004323 More actions · Answer 10

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.

Change is inevitable... Change for the better is not.

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