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


An Example of Test-Driven Development, Part 2


An Example of Test-Driven Development, Part 2

Author
Message
JJ B
JJ B
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1177 Visits: 2860
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.
Andy Leonard
Andy Leonard
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2425 Visits: 1102
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
Data Philosopher, Enterprise Data & Analytics
JJ B
JJ B
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1177 Visits: 2860
...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.
Andy Leonard
Andy Leonard
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2425 Visits: 1102
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
Data Philosopher, Enterprise Data & Analytics
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98540 Visits: 38996
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
icocks
icocks
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 631
Thanks Andy for an interesting article, like some of the others I'm not sure I can see myself going down the test-driven route in the same way, but it's good to see how it's done.

Interesting discussion about Alter vs Drop/Create. Here we Alter on the dev db, but deploy with drop/create (with an appropriate permissions script at the end). Only in extremis do we resort to backup/restore or shipping mdfs; apart from being poor practice in that it can mask broken code, there's generally too much hassle with realigning the permissions & getting round collation issues.

I know this is taking the discussion off-topic but I think there could be some mileage in a SQL Server Mythbusting article to debunk those things everybody 'knows' about SQL Server. Obviously the Exists thing is a prime candidate. Other ones that come to mind are 'Table Variables are always held in memory/Temp tables are always written to disk', 'Subqueries perform worse than joins', 'Cross Joins are always bad' and 'Cursors are always bad' (note the 'always' - I'm not disputing that they're abused an awful lot of the time :-) )

Cheers,
Ian
Andy Leonard
Andy Leonard
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2425 Visits: 1102
Hi Ian,

That's a good candidate for a Steve Jones Friday poll: What SQL Server myths did you once believe?

:{> Andy

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
Adam Gojdas
Adam Gojdas
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 1451

I know this is taking the discussion off-topic but I think there could be some mileage in a SQL Server Mythbusting article to debunk those things everybody 'knows' about SQL Server. Obviously the Exists thing is a prime candidate. Other ones that come to mind are 'Table Variables are always held in memory/Temp tables are always written to disk', 'Subqueries perform worse than joins', 'Cross Joins are always bad' and 'Cursors are always bad' (note the 'always' - I'm not disputing that they're abused an awful lot of the time :-) )


I would really like to see such a mythbusting article along with examples that disprove them. I know I have been guilty of believing some of them at one time or another before actually investigating them for myself.
asimsubedi
asimsubedi
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 23
nice
Robert Davis
Robert Davis
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6882 Visits: 1632
Adam Gojdas (7/13/2009)


I would really like to see such a mythbusting article along with examples that disprove them. I know I have been guilty of believing some of them at one time or another before actually investigating them for myself.


If you're not already reading Paul Randal's blog, this is a good reason to start. Paul Randal is the best myth buster out there, in my opinion, and has done a lot of posts on it. In fact, he did a full month of daily posts busting myths last year.

His blog: http://www.sqlskills.com/blogs/paul/
Myth busting category: http://www.sqlskills.com/BLOGS/PAUL/category/Misconceptions.aspx



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
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