It doesn't look like it from the code you showed, but are you using qualified names in the queries?

SELECT *

FROM schema.table

--not

SELECT *

FROM table

That would absolutely explain the problem.

According to the Upgrade Advisor column aliases in the ORDER BY clause cannot be prefixed by the table alias.

Why not try dropping the alias and then using the column name...

Generally, I use the COUNT(*). It's pretty much guaranteed to be accurate. Gail Shaw showed how it will pick an index with the least number of pages to satisfy the...

If you're comparing two different data types, char & nchar, then you'll get an implicit convert in the engine which will prevent the use of indexes. So yes, there is...

It's not considered to be a good practice to place procedures in the system databases (except for model). The caveats in the article are true, but they don't emphasize the...

Financial Testing Server

User Acceptance Testing Server

Performance Testing Server

Continuous Integration Server

Production Support Server

Sandbox Server

You can add them as you need them. They're all just SQL Server servers, just supporting different processes...

Bob Hovious (3/31/2009)

---

Hey Roy, didn't you hear? They started another thread that was invitation-only and you and I didn't make the cut.

... just kidding...

... at least to...

End 2 End Training are great guys. You'll learn a ton. I'm not sure who you'll be working with, but tell them Grant says hi.

That is really interesting. The plans themselves are almost identical, but the coaelesce is reducing the amount of data being accessed. Instead of 600 million rows, it's only 8 million....

Ken McKelvey (3/31/2009)

---

I assume that query performance tuning programs try every combination of join to see which is the most efficient. I have never seen one of these programs work...

Jack Corbett (3/31/2009)

---

Grant,

I'm reading it as 600 million rows (627,966,462) from the Full Text Engine.

I'd also be interested in the execution plan from the Coalesce function query.

You might also...

to rebuild or reorg is a kind of a hard decision. You can apply a rule and see how it works and adjust to meet your needs. Say 30% to...

james.spibey (3/31/2009)

---

Can you post the actual execution plan? Just save it as a .sqlplan file, zip it and attach it.

I see that your TVF is also calling another function. Just...

Yeah, that's exactly what I was thinking. It would prevent the use of the index. That's a concern usually, not a goal. That's why I'm a bit confused.

I've never heard about using COALESCE as a tuning mechanism before. What do you do with it and how do you format it? Is there a set of tests you...