SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automating tests for T-SQL code


Automating tests for T-SQL code

Author
Message
ladi.molnar
ladi.molnar
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 50
Comments posted to this topic are about the item Automating tests for T-SQL code
hakan.winther
hakan.winther
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2985 Visits: 612
I have not tried this tool yet, but if it works as mentioned, then this is what I am looking for. Smile
I will try this as soon as possible and get back to this forum, to share the experience.

/Håkan Winther

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Andy Leonard
Andy Leonard
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1416 Visits: 1096
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
Data Philosopher, Enterprise Data & Analytics
kramaswamy
kramaswamy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1978 Visits: 1812
looks very cool ... definitely considering trying this out
Charles Kincaid
Charles Kincaid
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 2384
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
Alexander Kuznetsov
Alexander Kuznetsov
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 824
Very interesting. Yet this raises a lot of questions:

1. IMO a very big problem with database unit testing is its sheer slowness:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/06/23/controlling-the-number-and-size-of-unit-tests.aspx

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:

http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes---testing-database-modifications/



4. To separate production code from test, have you considered moving test code to a separate schema and or separate database?
adrlinux
adrlinux
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 50
Hello,

It sounds very goog ! but is it works with sql server 2000 ?

Thanks for your answer.
dbunittest
dbunittest
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 6
I am glad to share one database unit testing tool. It is named as AnyDbTest (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.
ladi.molnar
ladi.molnar
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 50
No, it will not work with 2000. It uses features that are new in 2005 like TRY/CATCH.
ladi.molnar
ladi.molnar
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 50
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:


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.


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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search