Here is my main question, explained below: how can I unit test “functions” that lie in T-SQL scripts — but aren’t truly functions because they would severely impact query execution time?
I have hit a stumbling block with both SSDT and tSQLT — how to design reliable, correct, and repeatable tests of my code.
I am learning about how to unit test T-SQL code on my SQL Server database. I found Walkthrough: Creating and Running a SQL Server Unit Test (intended for use in Visual Studio in a SQL Server Data Project), but it appears that it is only designed to test stored procedures and functions. That page does not allow comments so I cannot directly ask the authors any questions.
(I am aware of tSQLt and am experimenting with that as well, but tSQLt has some drawbacks as well as strengths, particularly the inability to run tSQLt in a separate test project as of 2018-01-31. I am also aware of Redgate tools, but they charge money I do not have.)
Because of the performance issues, I have avoided use of stored procedures and functions in my code, which would make it trivial to test using SSDT Unit Tests. So rather than writing code like this that calls a function that can be easily tested and exists in one-and-only-one-place (i.e., testing the
dbo.GetComparisonDate(<date value>) function):
SELECT DISTINCT -- code... ComparisonDate = dbo.GetComparisonDate(l.ExitDate) FROM -- code ...
I have to write this, which vastly improves query speed, but which is not as easily testable.
SELECT DISTINCT -- code... ComparisonDate = CASE WHEN l.ExitDate IS NULL THEN @MostRecentThursday /* a variable declared earlier in the code block*/ ELSE dbo.GetMostRecentFundingDatePriorToGivenDate(l.ExitDate) END FROM -- code ...
What I need to do in this scenario is what would do in a C# unit test: build a set of tests with specific inputs and assert expected outputs, refactoring the actual query until I have accounted for all knowable cases.
The code sample I showed above is just one example of many column-based “functions” I need to perform in SQL Agent Job T-SQL steps. While I can use tSQLt to unit test excerpts of relevant code, I’m breaking the “do not repeat yourself” rule because I can’t really move those parts of my scripts into functions without making my SQL Jobs last hours instead of a few minutes.
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!