Gail, I ran that test set on SQL 2005 SP2 (can't install SP3 due to missing install code for SP2 apparently). I noticed that I have this index on...

use adventureworks

go

drop proc test

go

create proc test (@productid int = null, @quantity int = null)

as

set nocount on

select *

from [Production].[TransactionHistory]

where (@productid is null or @productid = ProductID) --indexed

and (@quantity is null...

Steve Jones - Editor (6/1/2009)

---

TheSQLGuru (6/1/2009)

---

Believe it or not Steve I have had not one but TWO hiring managers tell me in the last few months that my rate was...

Chris Harshman (6/1/2009)

---

TheSQLGuru (6/1/2009)

---

1) if you don't have indexes on fields that you are searching for then you have bigger problems than writing bad code.

2) If you are...

Actually I did in my "Time...

Chris Harshman (6/1/2009)

---

With everyone getting into their religious entrenched positions on what way is the “best” way to handle this, I think we all need to take a step back...

Steve Jones - Editor (6/1/2009)

---

I'm not a big fan of dynamic SQL, actually I hate it, but we try to present multiple points of view, and this is not a...

Personally I hope lots of people keep on doing this because due to the staggeringly-horrible performance this type of query can have there will be more opportunities for me to...

Uncle Moki (5/28/2009)

---

In a way, I hope you are right....even if only in the name...

Topical and timely words to the wise from Bart Duncan (someone who REALLY knows what is going on with SQL Server) and isn't just a pretender like myself! :w00t:

http://blogs.msdn.com/bartd/archive/2009/05/03/sometimes-the-simplest-solution-isn-t-the-best-solution-the-all-in-one-search-query.aspx

Short-circuiting in this case has nothing to do with a programming language but rather how the sql query optimizer was coded. If it was designed/programmed to do short-circuiting then...

GilaMonster (5/22/2009)

---

TheSQLGuru (5/21/2009)

---

I do not often waste my time coming up with such permutations as you did (or solving them) that are not relevant to the post. ...

Gianluca Sartori (5/21/2009)

---

Thinking on the performance side, I can only agree with you, but you can't forget that performance is just one of the qualitites of a good software: what...

ORIGINAL CODE:

select distinct @Result = sum( column1 )

from table (nolock)

where ((column2 = @param1) or (@param1 = 0))

1) why do you have distinct? you are doing a SUM, which can...

Amy Russell (5/19/2009)

---

We are definitely experiencing performance problems because the lack of memory for everything else on that box (backup, AV, etc.) causes the OS to page SQL Server out...