1) did the original table have an index on the field you are doing a max on and if so does that index still exist?

2) if an index doesn't exist...

1) the size of the database is actually irrelevant. You could have a 10TB database and a 2 row table that you are looking for dupes in. 🙂

2)...

sys.sql_modules.definition contains all code for sql objects. It should be used instead of syscomments if you are...

>>Ooohkaaay. I think I just ran into the scenario where the wrong partitioning can destroy your performance. Shame the documentation isn't a little more explicit on what NOT to do...

Very true words Steve, and the article you link to is spot on as well.

One exception I will take to what you said: "Someone that's worked with...

GSquared (6/3/2009)

---

The irony of a post complaining about misuse of...

1) You really cannot rely on sysdepends.

2) Nor can you rely on syscomments. That old mechanism has line-wrapping issues and you will miss strings that you should hit. ...

Perhaps because they found that...

1) I am curious what among these listed reasons where why you chose to do forced parameterization for your database. See here in BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/068282b7-c419-4e2c-990a-1f8b7c12762f.htm

I do have a client with...

Jeffrey Williams (6/2/2009)

---

Gail, I really don't want to argue this point - but I am having a small problem here. I have a query where I use this construct...

alter PROCEDURE Test4 ( @ProdID int = null, @Qty int = null)

AS

select TransactionID

from [Production].[TransactionHistory] -- with (index = [IX_TransactionHistory_ProductID])

where ProductID BETWEEN coalesce(@ProdID, 0) AND coalesce(@ProdID, 99999999) --should use actual limits...

Interesting. You also get a seek if you simply hard code values for coalesce (and I presume isnull) such as this example. It avoids having to hit the...

DOH!!! Good catch Dave. My brain sure did see seek last night...

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...