Ahh....good catch.

I was Assuming that the function used in the SELECT depended on the Row (as in a correlated subQuery).

Yes it very much depends on the function, what it's doing,...

function use in the SELECT List and or on the JOIN can also cause severe problems (maybe even worse then in the WHERE Clause).

by placing a Function in the select,...

I agree with you Brandie,

The Point is, I guess, is to try hard to reduce or limit the use of functions in a WHERE clause when and where you can.

If...

Ruben,

I'd build a @StartDate and an @EndDate Variable

Build them using your two parameters of Month and Year

Start date = 1st of Month

EndDate = last day of month

then use the Between...

yes agreed.

I would try to break the habit of using isNull() and other functions in your where clauses unless you are very certain you can consistently get the index Seeks.

definately...

Jeremy,

Excellent work.

it's important to prove assertions in articles like this.

all too often, folks just read stuff and believe it as the gospel.

Thanks for the kudos and for a great discussion...

Thanks for the Response...

Again, I'd have to see the exact DB to be able to reproduce the results.

All of our tables have clustered indexes and stats are updated.

I can pretty...

hmmm.....On our db, these resulted in scans 100% of the time and removing the functions (isNull Usually) fixed the issue 100% of the time.

I'd have to see specific examples in...

Thanks very much....

Yes, these are very common mistakes.

We had Hundreds of these occurances throughout our DB and the performance suffered immensely.

Took a few weeks to clean these all up and...

Andy,

Great Article, and very timely.

I just completed my MBA last night (literally) and then saw this article today....

I agree with what many have said. If you really want to move...

that's a good idea.

also, trace her queries and see if you can add some indexes to help her out.

my guess is that this person is querying the same stuff most...

spend lots of time on microsoft's msdn site for sql server

http://msdn2.microsoft.com/en-us/sqlserver/default.aspx

Greg J.

well....technically the reporting DBs never get smoked. they just get set to "Loading"

even while a DB is "loading" it's still usable. the client performance will just suffer as indexes are...

Good point.

that was another option we pursued but it did require down time (albeit short).

GAJ

Sreejith,

thanks for the post.

Glad you enjoyed the article.

I'll be posting another article in the next couple of weeks.

keep your eyes out for it....

🙂

GAJ