June 21, 2009 at 3:00 am
Comments posted to this topic are about the item Automating tests for T-SQL code
June 30, 2009 at 12:26 am
I have not tried this tool yet, but if it works as mentioned, then this is what I am looking for. 🙂
I will try this as soon as possible and get back to this forum, to share the experience.
/Håkan Winther
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
June 30, 2009 at 5:52 am
I like this idea a lot, Ladislau! I will dig in as soon as I can.
I'm writing a series on database development. One of the things I'm covering is the evolution of database testing. This looks like highly evolved database testing indeed.
Great work, and great thinking!
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
June 30, 2009 at 6:34 am
looks very cool ... definitely considering trying this out
June 30, 2009 at 8:44 am
We are doing the same thing with Visual Studio Team Server. The Test Driven Development thing is being integrated into our methodology.
Your tool looks to be a more affordable alternative. Great job.
ATBCharles Kincaid
June 30, 2009 at 8:57 am
Very interesting. Yet this raises a lot of questions:
1. IMO a very big problem with database unit testing is its sheer slowness:
How does an SP call
EXEC dbo.TestEquals 1.5, @MyVar
perform compared to an inline expression such as
SELECT CASE WHEN 1.5 = @MyVar THEN ... ELSE ... END
2. I see that 11 tests ran for 1 second. How much of that was setup/teardown? What time would it typically take to run 1000 tests?
3. When you cannot rollback, have you considered restoring from a snapshot:
4. To separate production code from test, have you considered moving test code to a separate schema and or separate database?
June 30, 2009 at 10:33 am
Hello,
It sounds very goog ! but is it works with sql server 2000 ?
Thanks for your answer.
June 30, 2009 at 7:38 pm
I am glad to share one database unit testing tool. It is named as AnyDbTest (http://www.anydbtest.com). After expanding its functionality in previous releases, AnyDbTest 2.2 brings a wide-ranging set of improvements.
Writing unit and component tests for objects with external dependencies, such as databases or other objects, can prove arduous, as those dependencies may hinder isolation. Ultimately, effective white-box tests isolate an object by controlling outside dependencies, so as to manipulate its state or associated behavior.
If you are an app developer, you can utilize mock objects or stubs to for controlling outside database dependency. But if you are a DB developer, these mock and stub frameworks cannot give you any helps.
AnyDbTest (Official website, http://www.anydbtest.com) provides an elegant solution for controlling a database dependency within applications by allowing developers to manage the state of a database throughout a test. With AnyDbTest, a database can be seeded with a desired data set before a test; moreover, at the completion of the test, the database can be placed back into its pre-test state.
Automated tests are a critical facet of most successful software projects. AnyDbTest allows developers to create test cases that control the state of a database during their life cycles; consequently, those test cases are easily automatic, as they do not require manual intervention between tests; nor do they entail manual interpretation of results.
The best way to see how AnyDbTest works is to read Quick Start.
July 1, 2009 at 3:16 am
No, it will not work with 2000. It uses features that are new in 2005 like TRY/CATCH.
July 1, 2009 at 4:05 am
Alexander, here are the answers to the 4 points that you raise:
1. Regarding database slowness.
You touch a good point; yes database slowness is an issue that has to be considered. I would go even further and say that SQL has a lot more limitations when it comes to unit testing. The entire unit test game must be viewed differently when applied to SQL compared with languages like Java / C#. There are a few core principles that remain intact but there are big differences.
Regarding the SP calls to something like Assert.Equals I am going to give more details at point 2.
2. Performance numbers.
I would like to know more about what were those 11 tests you are mentioning.
In my tests 1000 simple test procedures took just a few seconds.
On my machine, after a server restart, the first three runs of 1000 tests (see below) took 5233, 3740 and 3830 miliseconds.
My system is a dual core (AMD 64 5200+ 2.7 GHz) with 2GB RAM memory.
The test I did was meant to give an idea about the overhead that the TST API has. Here is the test:
a. I created a database with a simple function (TFN_DoubleInt) that simply multiplied the input parameter with 2 and returned the resulted value.
b. I added 1000 different test stored procedures. Each looked something like this:
[font="Courier New"]
CREATE PROCEDURE SQLTest_PN
AS
BEGIN
DECLARE @ActualValue int
DECLARE @ExpectedValue int
SET @ActualValue=dbo.TFN_DoubleInt(N)
SET @ExpectedValue=2 * N
EXEC TST.Assert.Equals 'SQLTest_PN', @ExpectedValue, @ActualValue
END
GO
where N is a number between 1 and 1000.
[/font]
3. Regarding rollback & restoring from a snapshot
At this point I think that the cases where the auto rollback will not be able to perform will be fairly rare in practice. Also, when that happens it is a consistent thing not an error like event. TST does a good job of detecting those cases and there is no risk that it will simply let them slide. In that case the user can with a little work do the clean-up himself in a TEARDOWN. But it all depends on the feedback I will get. I am open to that if it becomes a concern and is requested by enough people.
4. Separating production code from test. Separate schema and or separate database.
Yes, definitely you can do any of those. You can isolate all the test procedures in a separate database if you wish. Also you can place them in the production DB but isolate them in their own schema.
October 17, 2012 at 5:23 am
Hello,
I just wanted to highlight an article that I had published on InfoQ on how to test with TST - more than dealing with details on TST API, it encourages BDD principles to breakdown tests into meaningful and reusable steps and making them maintainable.
http://www.infoq.com/articles/tst-sql-server
Wondering whether you could read and give your thoughts!
Best Regards,
Roopesh
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply