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

An Example of Test-Driven Development, Part 2 Expand / Collapse
Author
Message
Posted Tuesday, June 16, 2009 11:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:37 AM
Points: 266, Visits: 2,585
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.
Post #735900
Posted Tuesday, June 16, 2009 4:47 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:44 PM
Points: 389, Visits: 1,041
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
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #736094
Posted Tuesday, June 16, 2009 5:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:37 AM
Points: 266, Visits: 2,585
...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.
Post #736099
Posted Tuesday, June 16, 2009 6:19 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:44 PM
Points: 389, Visits: 1,041
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
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #736125
Posted Tuesday, June 16, 2009 7:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
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.



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)
Post #736139
Posted Wednesday, June 17, 2009 2:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:06 AM
Points: 47, Visits: 599
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
Post #736279
Posted Wednesday, June 17, 2009 6:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 12:44 PM
Points: 389, Visits: 1,041
Hi Ian,

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

:{> Andy


Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #736453
Posted Monday, July 13, 2009 9:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 79, Visits: 1,330

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.
Post #752165
Posted Monday, October 18, 2010 2:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 12, 2012 3:41 PM
Points: 4, Visits: 23
nice
Post #1006030
Posted Thursday, August 16, 2012 10:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:01 PM
Points: 1,613, Visits: 1,539
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1346102
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse