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 12:09 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Tuesday, July 8, 2014 9:43 AM
Points: 569, Visits: 1,009
Comments posted to this topic are about the item What is the Best Way to Test SQL?
Post #745099
Posted Wednesday, July 1, 2009 6:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 7, 2012 9:23 AM
Points: 304, Visits: 716
Good editorial Tony, but one line you have in there jumped out at me...

"...few DBAs and Database Developers have the required .NET skills..."

I would really love to know just how true this statement is - just out of curiosity - because in our work you must have both and though we have not posted for openings in a year or more, when we did, .NET skills were one of the top requirements for any DBA who is going to work in any of our companies. In fact (and it may just be our organization), any DBA who did not have some .NET familiarity, to me, is not a DBA. I mean, thats is kind of like hiring a carpenter who knows it all, except how to use a hammer and nails.

What good is it to tune SQL and then find someone setup a data connection in .NET incorrectly? I mean, isn't SQL the backend for ASP.NET and Winforms .NET projects - as it is in our organization? So, tuning SQL and not knowing the proper methods and best practices in .NET sounds about as useful as tuning up a car that has no wheels. Engine runs great, but cant go anywhere.

I don't know whether I am just old and therefore old-fashioned, but what good is any database knowledge when you don't have a clue about the "presentation layer" and how it operates - whether its Web ASP work, or good old Winforms work.

Our DBAs work with our developers and we test in our QA departments - but all those people have knowledge of both SQL and .NET. I don't see how you can separate those two things. Sounds kind of like my cooking ability - I am great at cutting up vegetables, boiling water, opening packages - but I cant produce even a good toaster waffle!


There's no such thing as dumb questions, only poorly thought-out answers...
Post #745309
Posted Wednesday, July 1, 2009 6:59 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
On the main subject, I've had trouble in some places getting devs to take the time to test anything beyond what they themselves write, and even then getting them to test it by doing anything more than firing up a copy of the modified/new web page and trying a few inputs has been a major effort.

I have my own test systems for database code changes. They're pretty thorough.

On the subject of knowing .NET and T-SQL, I have to say that I know enough VB.NET to be able to read it, and enough C# to recognize it, but not much beyond that. Used to be fairly sharp with VBA, but that was years ago. On the analogy of tuning a car, I'm the guy who can rebuild your engine from scratch, but who won't touch the body work. You can chose to go to a guy who can do both, but I can pretty reliably assert that he won't do as good a job on the engine as I will, nor will he do as good a job on the body as a dedicated specialist would on that.

Outside the analogy, the best work I've done as a T-SQL Architect and Dev, was where I specialized in the database, and two other people specialized in the .NET parts, and we produced a heck of a great system.

That's not to say that some few people can't be really good at both, but given the same resources (intelligence, training time, experience, etc.), the more specialized a person is, the better results he'll usually get in that specialty. That's one of the major foundations of human civilization.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #745325
Posted Wednesday, July 1, 2009 7:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:09 AM
Points: 6,580, Visits: 8,856
GSquared (7/1/2009)
On the subject of knowing .NET and T-SQL, I have to say that I know enough VB.NET to be able to read it, and enough C# to recognize it, but not much beyond that.

Excellent comments Gus, and I agree. On the quoted part, I would fall into this same category. Help (F1, Intellisense, google) is my best fiend when dealing with .NET.

That being said, if you're going to be working in SSRS (or CLR programming), you really do need to learn at least the basics of .NET (if I remember correctly, in SQL 2005, it is VB.NET; while in SQL 2008 it can be either VB/C# .NET).


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #745380
Posted Wednesday, July 1, 2009 7:58 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
What has attracted me to testing frameworks is the prospect of "automated and repeatable" tests. I want to be able to make a change to a subsystem, run the test suite and see pass and fail on all the essential features of the subsystem. If everything passes, then I can proceed with more confidence. Also, others can do the same. So, I've been using TSQLUnit for projects that go beyond basic and can benefit from "automated and repeatable."

While setting up the tests, I have to keep the longer-range goals of "automated and repeatable" in mind--because the initial tests for the existence of objects adds development overhead in the short term to meet those longer-term goals.

In the TDD (test-driven development) theory I've been exposed to (from Alistair Cockburn--Crystal Clear) tests are to be designed to fail until the minimum feature is put in place at which point development ceases. This keeps development very focused and organized as one feature or tightly-coupled feature set is developed at a time. This benefit alone helps justify the added effort for me.

So in practice my first test for say developing a stored procedure is to create a test for the existence of the stored procedure. All that's been developed at this point is a decision about the name of the stored procedure. Not very exciting. However, if someone down the line changed the name, the test would fail and bring the change to attention.

Questions at this point arise for me. It's one thing to check for the existence of a table. But what about a check for the existence of each column along with the data types, constraints, collation and so forth? I haven't to this point set up a lot of tests for DDL statements, but rather rely on a different tool for comparing versions of objects: Red Gate SQL Compare. I might try more DDL tests now that I'm familiar with INFORMATION.SCHEMA.

I find the TSQLUnit set up and tear down very useful. I can create a set of test rows here, either hand-picked or built from another tool such as Red Gate SQL Data Generator for testing extremes in values data types and handling of NULLs.

Also, I build tests for the existence and proper implementation of check constraints by running insert, update and delete operations on test rows and then check to make sure only those operations that should result in an insert, update and delete actually do.

Tests for performance is an interesting area. Tests are best done if discreet--so performance tests should be separate. These probably belong in their own "suite." Again, the set up and tear down features of a framework come in handy, along with the ability to build large sets of rows. The repeatable nature of tests can really help with performance testing in conjunction with SQL Profiler and other built-in SQL Server tools.

I haven't found a good way to automate tests for concurrency for T-SQL within a T-SQL framework--and perhaps that's just as well. Perhaps it's better done externally with a simulation of front-end application use.

At the end of the day, tests can't replace decision logs and documentation--so those belong there as well.

With the use of a framework, though, the days of relying on someone's afterthought ad-hoc manual tests are gone. Later if a bug is found or an enhancement is needed, more tests are added to the suite and away we go.


Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
Post #745408
Posted Wednesday, July 1, 2009 8:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:25 PM
Points: 33,051, Visits: 15,160
I'd tend to agree with Gus. I think lots of DBAs don't know .NET very well.

Blandry, I think you're in a place where you are looking for more well rounded people. There are tons of DBAs that just administer servers, they don't do, or even deal with, any development.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #745425
Posted Wednesday, July 1, 2009 10:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:30 PM
Points: 352, Visits: 1,868
Personally, I am a huge fan of the DbFit tool (hence: http://www.sqlservercentral.com/articles/Testing/64636/). I like it because it not only provides repeatable, automated testing (both from GUI and from command-line), but it is a very flexible framework that allows for most situations. Granted, to be clear we are talking about functional testing, not performance testing; Unit testing does not deal with performance. But compared to the *Unit (or would that be %Unit ) frameworks, DbFit does not require any modifications to your DB in order to test. You do not need to create any test procs nor any setup / tear-down procs. All of that is contained within the test itself which by default is a transaction that gets rolled-back at the end (although you are allowed to break out of the automatic transaction for more control). We use DbFit quite a bit where I work as well as regular FitNesse for our Continuous Integration and it works quite well. And again, our database (both schema and procs) has no knowledge of the DbFit tests even existing; they are completely isolated. Just yesterday I created a DbFit test page that connected to one database and ran a query while storing the result set in a variable, and then on the same page it connected to another instance and ran another query against our data warehouse and stored that result set and then at the end of the test I compared both result sets to make sure that the source data was being moved to the warehouse server and aggregated correctly. This was quite easy to do and exists entirely in a single DbFit wiki page. I am even able to create temp table and temp procs in the tests for the purpose of the test that simply go away at the end of the test.

And the framework is so easy to use that in most cases our QA folks create the DbFit tests themselves since it requires just basic SQL knowledge. They have set up some elaborate tests that pass variables from a top-level page down to sub-pages, etc.





SQL# - http://www.SQLsharp.com/
Post #745585
Posted Wednesday, July 1, 2009 1:08 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
"...few DBAs and Database Developers have the required .NET skills..."

I heartily agree witih this statement. And from my experience I would add:

"...few application developers have the required database and SQL skills. I've been on both side of the argument for many years, so can speak fairy well to this.

I spend a large portion of my time researching and finding fixes for logic and performance issues with SQL written by developers. On the other hand, I wouldn't expect them to tolerate my dwindling or extinct front-end efforts.

Just this week I heard the common developer tech-babble: "Well, it only fails at the end of each quarter". In the meantime, the end-user was without data for ten seperate days in the month of June.

Post #745709
Posted Wednesday, July 1, 2009 1:16 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
Yes, lots of DBA's don't know .Net very well. I know probably the least. But I'm a *&^% good DBA and I do far more than rerunning failing jobs and doing backups. I'll offer my SQL skills against anyone in our organization. We build servers, manage patching, debug failed jobs, fix bad data from poorly written code, and fix bad SQL code that developers can't. It ain't an ivory tower.
Post #745712
Posted Wednesday, July 1, 2009 2:01 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
blandry (7/1/2009)

"...few DBAs and Database Developers have the required .NET skills..."

I would really love to know just how true this statement is - just out of curiosity - because in our work you must have both and though we have not posted for openings in a year or more, when we did, .NET skills were one of the top requirements for any DBA who is going to work in any of our companies. In fact (and it may just be our organization), any DBA who did not have some .NET familiarity, to me, is not a DBA. I mean, thats is kind of like hiring a carpenter who knows it all, except how to use a hammer and nails.


"Jack of all trades, master of none." Seriously, there's so much in SQL and so much in .net I don't see how someone can know both really, really well. Specialise in one and have a basic understanding of the other or know both relatively well.
Quite a few DBAs come from the sysadmin side and therefore know SQL and windows admin but have never opened a dev tool.

I came from the dev side, but I haven't written front end code seriously in about 5 years. I can hack up a quick app if I need to, but it takes ages and real .net devs laugh at my efforts. These days I spend all my time in the DB engine and there's still large parts of that I don't have time to get familiar with

GSquared (7/1/2009)

On the analogy of tuning a car, I'm the guy who can rebuild your engine from scratch, but who won't touch the body work. You can chose to go to a guy who can do both, but I can pretty reliably assert that he won't do as good a job on the engine as I will, nor will he do as good a job on the body as a dedicated specialist would on that.

Agreed. Often I find small shops want the one person who can do both, because they either don't have enough work to 2 people busy, or they can't afford or justify 2 staff where one can do both roles.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #745735
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse