Mocks for SQL
Mocking is not a term usually heard in conjunction with SQL development. An object oriented program may use a mock to emulate data returned from a database in order to test the program without requiring a database to be present. The mock simply acts as a substitute for the database engine.
Why not use a mock when writing SQL code such as stored procedures or functions? Sometimes stored procedures or functions become sophisticated in the business rules they are implementing. I have heard SQL developers say that they cannot build any automated tests against their code because the data is time sensitive, or constantly changing. My reaction is that there is no way you can really test your code in that kind of environment.
At the end of the day, the only way to be assured your code does what it is intended to do is to have a fixed input with a fixed result. I have seen some that want to have variable input with variable results. What happens in that case is that you end up writing your business logic twice…once to perform it, and once to test it. Either form of code may be wrong.
Here are a couple of ways you can mock data for testing
One way to mock is to use #temp tables. If a #Temp table exists prior to calling a stored procedure, then the table and its contents are in scope of that procedure and may be used to exercise the code. So, if you have a #temp table containing the input to code you are testing, you can write code that has a static expectation of the final results.
Another way to use a mock when writing code in SQL Server is to use a Table Value Parameter(TCVP) as input to the stored procedure or function. This works essentially the same way as using a #temp table.
Regardless of method used, your test would instantiate an instance of the #Temp table or TVP, call the stored procedure or function, and then evaluate the results. If the results are a table then table contents may be compared with another table containing expected results. If the stored procedure or function returns scalar output then you can simply test for the expected results.
Tie this kind of testing in with automated testing tools such as NUnit or MS Test and you have an automated test of your database code resulting in a high degree of confidence for success when modifying or extending your existing database code.
Tip of the Day
I saw a tip on a forum with a thread about TempDB running out of space. Thought I would pass this on. The discussion was about TempDb growing until the disk was full. How do you get TempDb to shrink? We had a thread on that a while back, so I won’t get into that particular issue.
One contributer to the thread wrote that they create a bogus file on the drives where SQL files are placed taking 3-5 gig. Then, when disk alerts occur, they can quickly regain some needed space by deleting the bogus file. I think this idea is great in an environment where disk space is limited and the data requirements do not grow in a predictable manner.
If you include this tip along with alerts, it buys you time to respond to disk constraints quickly before the whole system becomes stressed and un-responsive.
Cheers
Ben
$$SWYNK$$
Featured Article(s)
Columnar Databases and SQL Server Columnstore Indexes
In this article I will talk about columnar databases, how they are different from the row store databases that we use every day and why do we need them. Also, I will try to figure out what SQL Server has to offer with Apollo and how it stands up to any other column store database.
Featured White Paper(s)
Key Management in the Multi-Platform Environment
Written by Townsend Security
This White Paper discusses the challenges … (read more)
Featured Script
sp_show_huge_tables
To list the size of all tables in the database in descending order (that is bigger tables first)…. (read more)