Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

What is the Best Way to Test SQL? Expand / Collapse
Author
Message
Posted Wednesday, July 1, 2009 4:50 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:32 PM
Points: 3,428, Visits: 14,439
Over the past five or so years, encouraging progress has been made with regard to testing .NET application code. Unit testing tools such as NUnit and xUnit have moved from the XP fringes into the mainstream, and most IT departments are at least starting to become aware of the importance of thorough unit testing, as well as regression testing, load/performance testing, integration testing, and so on. I wish I could say that the same progress had been made with testing SQL.


I think most big companies especially software engineering companies run formal testing process where a developer do just unit testing all other tests are run by QA team.

Why is this? Some Database Developers with C# skills have adapted established NUnit techniques to test their database code. However, few DBAs or Database Developers have the required .NET skills, making this a hard path to follow. In addition, this approach represents another instance of the "object-relational mismatch", with an object-oriented tool being used to test a relational model.


I do a subset of both SQL Server and .NET as in Asp.net using C#, I don't know anything about Winforms, SMO, Replication and other areas of SQL Server which I think are less related to application development. What I don't know in both is by choice so I can be good at what I choose to do that is one of the reasons I think Microsoft needs to separate SSIS to ETL and admin because both are not related.

I also think SSIS package running transforms for data from Oracle, Sybase, DB2 and MySQL can be called in OSLO which can be consumed by all applications in the Microsoft platform. Implementation detail will not be complicated because there are the five to six transaction implementation by Microsoft.


Kind regards,
Gift Peddie
Post #745855
Posted Thursday, July 2, 2009 6:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:09 PM
Points: 111, Visits: 541
Solomon Rutzky (7/1/2009)
Granted, to be clear we are talking about functional testing, not performance testing; Unit testing does not deal with performance.

Solomon suggests that unit testing doesn't deal with performance features. Is this a universally-understood rule? Thank you.


Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
Post #746213
Posted Thursday, July 2, 2009 12:48 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:23 AM
Points: 368, Visits: 1,950
Bill Nicolich (7/2/2009)

Solomon suggests that unit testing doesn't deal with performance features. Is this a universally-understood rule? Thank you.


Hey there, Bill. I guess you ultimately want someone else to answer that question since I will only confirm what I said , but I can add to it first.

It seems that there is a general misunderstanding in the DB world of what Unit Testing is. Unit Testing is supposed to test a very specific piece of functionality. Given a set of inputs and outputs it can be determined whether or not a particular set of logic is working as expected. This is irrespective of how quickly and/or efficiently it works. It is just functional testing on a particular method / function / procedure.

A further complication is that true Unit Testing tests ONLY that piece of logic regardless of any external dependancies that piece of logic might have. For instance, when doing Unit Testing of a C# method, if that method makes a DB call or a call to another method, those external calls are "mocked" with given return values so that the piece of logic being Unit Tested is truly isolated. There is no way (that I know of) to "mock" external calls within a Stored Procedure. So if you test a Stored Proc that calls another Stored Proc or a User-Defined Function, then you are not doing true Unit Testing since you will really be testing the Proc you intended to test plus anything it calls. And even if you could "mock" external Proc or Function calls, what would you do in the case of a Trigger. And the issue of whether or not data constitutes an external dependancy is another story, but maybe not as important because it can be, to a degree, isolated within the scope of a transaction.

So in the DB realm we do functional testing (that sometimes people call Unit Testing) the best we can. And again this is why I like DbFit: it facilities easy, and repeatable / automated testing without requiring any updates to the DB for testing. You do not need to add any testing procs to the DB in order to test. This also makes the tests very portable since you can simply change connection strings and not have to worry about "deploying" test procs and keeping those up to date in each environment & server.

Take care,
Solomon...





SQL# - http://www.SQLsharp.com/
Post #746561
Posted Thursday, July 2, 2009 2:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 17, 2011 7:58 AM
Points: 29, Visits: 117
To everyone following this train of thought, I think maybe the reality is that there are any number of ways of testing SQL, and I suspect that each of us has our favorite means and/or the means we are most competent to use. Some require additional software, some require additional knowledge and/or intuitive capabilities. I would submit that whatever the tool or level of abstraction, the intuitive ability of the tester is the most critical of all.
Although many if not most of you might not agree with me, my tool of choice is still SQL itself. I've tried other solutions myself, had worked with teams using various testing and debugging packages, and what I've seen is that the tools only go so far and are never as effective as plain old breaking the SQL into elements and making sure we understand exactly what each piece is doing.
One of my functions as an 'oldtimer' has been to mentor a number of folks new to the DBA world, and we don't depend on tools that the company may or may not decide to and continue to purchase/license or whatever. You need to understand SQL and be able to function whether tools are available of not. And that is how you find the most hidden of bugs or performance problems.
Post #746632
Posted Thursday, July 2, 2009 2:33 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:09 PM
Points: 111, Visits: 541
The newest MCTS exam 70-433 Microsoft SQL Server 2008 Database Development test so far to my knowledge is completely silent on test-driven development techniques.

The team system of Visual Studio has had tools for creating test rows for a while and has versioning which can document database objects in an all-at-once sense. Many including myself don't have a team system license. I use the Red Gate tool to build rows.

I don't know that there are many resources found at MSDN or other Microsoft-sponsored learning/reference resources on TDD for database development either.

It looks like we're still in the wild wild west of sorts on this. The original question is a good one because there's not a lot of authoritative sources right now to opine on the matter.

Another question that I'd like to broach is the question of "to what extent should we test and when?"

It would be a stretch to say that everyone should write a test before they write any code for database development, period.

Tests assume solid requirements. In the early stages, requirements might be fluid and specific tests might not be available. If we're working in Oslo and using MSchema, creating entities and defining relationships and having our T-SQL code expressed for us, then we're writing code before developing tests for it and the schema is changing with relative frequency. So I think there's an obvious collision going on between test-driven development and the newer model-driven development techniques.

Alistair Cockburn, one of the founders of the Agile movement says this: "It is clear to most people by now that no one methodology will fit every software project." This can be applied to test-driven development as well. I think there's "too light" and "too heavy" an application of TDD for any given project.

If you're working on a nuclear reactor with a dozen other database developers simultaneously, then you might want to write all tests first before creating anything. Maybe you'd go to INFORMATION.SCHEMA and test for the existence of your table, all your columns with their data types, constraints and so forth before you actually create the table. But it might seem odd to write a test first for a table when you're the only developer working on a simple application where there are no bad effects to speak of from a bug.

I think Cockburn's Crystal "mineral analogy" can guide us. Minerals have hardness and color--or two axes. The color changes from clear to yellow, orange, red, magenta and blue as then number of developers and size of the application go up. Let's say that's the X axis. The Y axis is "hardness" which describes how critical the system is. A nuclear reactor is critical along with other life-critical applications--so those would be "hard." Applications get softer from there the less that's on the line due to a bug.

So perhaps less formal testing is sufficient for smaller, less critical projects. That said, I think all database developers would benefit from being able to write formal tests and use a framework where they can repeat tests for when a project calls for it.


Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
Post #746639
Posted Thursday, July 2, 2009 2:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 17, 2011 7:58 AM
Points: 29, Visits: 117
Right on, Gila Monster. If you have high blood pressure or hives, you genarlly can 'get by' going to a GP. If you have cancer, don't you go to the best specialist you can find? Why should hiring be any different? You get what you pay for.

Post #746643
Posted Tuesday, October 27, 2009 9:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 9:45 AM
Points: 2, Visits: 1
Hi there, i would also like to know what is the best way to test SQL since there is no interface.

I need to test merging, unmerging, dedupe, match rules and start propgation. Please help
Post #809378
Posted Tuesday, October 27, 2009 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 9:45 AM
Points: 2, Visits: 1
Hi there, i would also like to know what is the best way to test SQL since there is no interface.

I need to test merging, unmerging, dedupe, match rules and start propgation. Please help
Post #809380
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse