June 15, 2009 at 11:57 pm
Comments posted to this topic are about the item An Example of Test-Driven Development, Part 2
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
June 16, 2009 at 7:21 am
"One reason is it doesn’t matter whether I execute an Alter Procedure statement or a Drop and Create – the changes to the Procedure are accomplished regardless."
Don't forget that if you drop the procedure then you loose the permissions.
June 16, 2009 at 7:29 am
That's an excellent point William, and I did not address permissions in this article. Good catch.
You can probably already guess that I'd script the permissions in re-executable T-Sql.
Thanks for pointing that out!
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
June 16, 2009 at 7:52 am
I got turned off by the apparent lack of understanding of how to use "IF EXISTS". Do you really think you need to do concatenation there??? You don't need to specify any columns in your SELECT when all you're doing is checking for EXISTS. "IF EXISTS(SELECT 1 FROM ... WHERE ...) will get you the same result, although with slightly better performance. When I start reading an "article" that shows this kind of lack of basic understanding I don't waste my time getting to the end of it. Who knows what kind of crap I could get stuck in my head if I wasn't careful.
June 16, 2009 at 8:16 am
I believe Andy's intent in identifying specific fields in the EXISTS statements is to more clearly illustrate the EXISTS condition that is being discussed. Also, when you write SQL that has to be maintained over the course of years or decades, having that extra information can be beneficial to someone debugging the code.
June 16, 2009 at 8:33 am
You should get the same performance either way. The SELECT list will never get processed in the EXISTS predicate; you can put anything you want there. EXISTS checks for existence of a row, not whether something in the SELECT list is satisfied. Check out the following two batches; notice any difference in the query plans? Neither do I.
if exists (select name+'abcd' from sys.databases)
print 'abcd'
go
if exists (select * from sys.databases)
print 'abcd'
go
I've taken this one step further in the past by creating a CLR UDF that sleeps the thread for 30 seconds every time it's called. Calling that function in the SELECT list of an EXISTS predicate has absolutely no effect on performance. Try it yourself if you believe this really has any impact.
--
Adam Machanic
whoisactive
June 16, 2009 at 9:03 am
bstauffer (6/16/2009)
I got turned off by the apparent lack of understanding of how to use "IF EXISTS". Do you really think you need to do concatenation there??? You don't need to specify any columns in your SELECT when all you're doing is checking for EXISTS. "IF EXISTS(SELECT 1 FROM ... WHERE ...) will get you the same result, although with slightly better performance. When I start reading an "article" that shows this kind of lack of basic understanding I don't waste my time getting to the end of it. Who knows what kind of crap I could get stuck in my head if I wasn't careful.
Seems like you've already got something stuck in your head that shouldn't be there (you're not alone though, as I've seen the above asserted many times before).
There's no difference at all in performance between an Exists query with columns selected against that with a constant selected. Don't take my word for it -try creating yourself a wide & long table & see if you can spot the difference in performance & query plans between an Exists (Select *...) vs an Exists (Select 1...).
It's true there's no functional difference, so the sole point of specifying anything inside the Exists clause is to aid understanding.
I assume the performance issue was true at one point - I know it's not true of SQL 2005 at least, and I'm pretty sure it's not true for 2000 either. Anybody know where this stems from?
June 16, 2009 at 9:20 am
icocks (6/16/2009)
Anybody know where this stems from?
It stems from assumptions made by people who didn't know any better, repeated over and over until those assumptions became assumed truths. These assumed truths found their way into "best practices" and "tips and tricks" documents and Web pages, which people generally just take at face value, spreading them even further and teaching others.
Moral of the story: Always ask questions. Always test. Never take any advice at face value.
It ain't what you don't know that gets you into trouble. It's what you know for sure that just ain't so.- Mark Twain
--
Adam Machanic
whoisactive
June 16, 2009 at 11:06 am
I learned the "SELECT 1" thing way WAY back in SQL 6.5, maybe even SyBase SQL.
Sorry for the tone of my post initially. We're in a pre-deployment test cycle here and it's a bit stressful.
June 16, 2009 at 11:38 am
Thanks Andy, for posting this article. As a long time fan of TDD and TD3 I found your approach of using SSMS solutions/projects interesting.
Over the years I have worked on data-centric projects and have used SqlUnit, TSQLUnit, dbUnit, etc. These testing frameworks work great for TD3 but require that database developers write tests in XML. Not a big deal, but it can be off-putting for some developers.
On my most recent project I introduced our team to DbFit (http://gojko.net/fitnesse/dbfit/) Gojko Adzic's extension of Fitnesse for database testing. Team members adapted easily to the rhythm of: write a little test; watch it fail; make it pass; make it better; repeat. The DbFit fixtures kept us from having to do the initial "heavy lifting" needed to get our tests running.
Another benefit of database test automation is the ability to run all tests in an automated build (continuous integration) environment. This is another topic that deserves to be introduced to database developers.
June 16, 2009 at 11:56 am
I appreciate this article and gave it 5 stars. This article went further (as expected) in explaining how test driven development might work at the database level. I like how you write clearly and with very good examples. I'm still not sold on the usefulness of this technique -- at least in my environment -- but I'm glad to learn how test driven development works for databases.
That said, I found it interesting that I already do a small part of what you are demonstrating. I never check for the existence of a database or table or schema. However, all my scripts for code pieces (stored procs, functions, views, etc.) start with checking to see if the proc/etc. exists and if so, it drops it. Then I create the proc/etc. Then I check for existence. Then I apply permissions. All of this is part of my templates for each code object type so it is easy to incorporate and never forget. I may not do it exactly the way you do it, and I don't go beyond testing for existence (formally with unit tests), but I was happily interested that there is some overlap.
I also find your description of developing with solutions/projects helpful. I don't do it that way and am interested to see how it works. I appreciate that you take the time to explain the environment and do not assume we already know it.
Thanks for writing this up.
June 16, 2009 at 4:47 pm
Thanks all for the feedback! Keep it coming, please.
First: Bstauffer, we all have rough days. I'm glad you brought up the point you did because I learned from Adam's and Icocks' responses. William's response was closer to why I did it that way - I like to "manage" the test conditions, and in this case "managing" them means writing a condition in the IF EXISTS statement that I can highlight and execute in an SSMS query window and view the results I want in a format I prefer. I'm by no means asserting this is right or even good (and I wouldn't dare call it a "best practice"). I simply say it works for me now. I'm still learning - I learned something today about IF EXISTS - how cool!
I'm really impressed by the fact you added another comment. That was very cool of you - thanks!
William, thanks for the comment about ALTERing procedures and the defense. As I said, your reason was closer to my own.
Adam and Icocks, thanks for schooling me! I probably would have offered the defense that the script should run a minimal amount anyway, and shouldn't be performance-tested; but it would have bugged me that I wrote something that may not perform well - even if it only costs a few milliseconds per year. I'm not making that up: I am an engineer. It would have bugged me.
Agilist, you've given me homework. I want to know more about DbFit! I am with you on CI for database development (and actually for BI development as well). I've been preaching this since the PASS Summit 2007 and, in my opinion; Microsoft is starting to give us the building blocks we need - integrated, even. One step at a time, right? We'll get there.
My only defense for beginning this series where I did is: I want to start at the footing of the foundation for where I'm going, which is Continuous Database Integration (CDI anyone?). Yes, I am digging some. Yes, it's hard work. But I am attempting to cover fundamentals in their primitive form.
JJ B, thanks for your kind words. You and William make a strong case for ALTER vs. DROP / CREATE for procedures. I don't disagree with your approach. I hate recreating permissions - I always forget one! And that completely slipped my mind. I'll have to incorporate it into a future article in the series and give you both credit for pointing that out.
I like to write "buffet" articles. There's some stuff here you want to try, give it a try. It's safe - it's just a little article and snippet of demo script you can whip up in a jiffy. If it looks really awful and you don't have the stomach for some piece, just move along... maybe the next thing will be more appetizing. That's my philosophy.
I'm working on article 3 in the series. Sneak peek: The two main points are refactoring and auto-generating some of these tests.
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
June 16, 2009 at 5:01 pm
...and give you both credit...
Not me. I don't deserve any credit. I actually do the DROP, *not* alter. I happily include permissions creation at the end of the script. I don't have any problem with that approach.
I prefer writing up permissions at the bottom of every script. One thing you and I share in common is that I set it up so that I can re-create my databases (sans data) from scratch simply from scripts. This includes tables, procs, views, etc, and permissions. I can open any script file and know what permissions apply for that object. If I need to change permissions, I always do it through the script.
June 16, 2009 at 6:19 pm
Interesting JJ B,
I get some resistance from some about deploying database via script. I think it standard operating procedure in a lot of shops to restore backups and call that a deployment. I disagree. Sounds as if you do as well.
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
June 16, 2009 at 7:35 pm
Two things.
One, with SQL Server 2005 and on tracking when objects are modified, I would be more inclined now to use ALTER instead of DROP/CREATE.
Two, I agree with deploying a database via scripts. There may be changes as the database is going to production.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply