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
Andy Leonard
Andy Leonard
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: 1194 Visits: 1095
Comments posted to this topic are about the item An Example of Test-Driven Development, Part 2

Andy Leonard
Data Philosopher, Enterprise Data & Analytics
William Mayo
William Mayo
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 139
"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.
Andy Leonard
Andy Leonard
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: 1194 Visits: 1095
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
Data Philosopher, Enterprise Data & Analytics
bstauffer-926504
bstauffer-926504
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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.
William Mayo
William Mayo
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 139
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.
Adam Machanic
Adam Machanic
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1635 Visits: 714
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
icocks
icocks
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 628
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?
Adam Machanic
Adam Machanic
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1635 Visits: 714
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
bstauffer-926504
bstauffer-926504
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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.
Agilist
Agilist
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
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