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 Monday, June 15, 2009 11:57 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 6:49 PM
Points: 389, Visits: 1,042
Comments posted to this topic are about the item An Example of Test-Driven Development, Part 2

Andy Leonard
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #735469
Posted Tuesday, June 16, 2009 7:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 11:54 AM
Points: 6, Visits: 137
"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.
Post #735640
Posted Tuesday, June 16, 2009 7:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 6:49 PM
Points: 389, Visits: 1,042
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
CSO, Linchpin People
Follow me on Twitter: @AndyLeonard
Post #735647
Posted Tuesday, June 16, 2009 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 10, 2010 2:01 PM
Points: 5, Visits: 22
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.
Post #735671
Posted Tuesday, June 16, 2009 8:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 11:54 AM
Points: 6, Visits: 137
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.
Post #735690
Posted Tuesday, June 16, 2009 8:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:37 AM
Points: 1,140, Visits: 703
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #735710
Posted Tuesday, June 16, 2009 9:03 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
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?
Post #735748
Posted Tuesday, June 16, 2009 9:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:37 AM
Points: 1,140, Visits: 703
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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #735770
Posted Tuesday, June 16, 2009 11:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 10, 2010 2:01 PM
Points: 5, Visits: 22

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.

Post #735862
Posted Tuesday, June 16, 2009 11:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 29, 2011 3:47 PM
Points: 1, Visits: 7
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.
Post #735884
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse