Wow, what a fantastic post! That's a really clear explanation of what's happening and will help me out a lot. I may have somewhat of a problem in that I...

Running the following

SELECT COUNT(*) FROM [dbo].[CAT_fctSearchProducts](' FORMSOF (INFLECTIONAL, tube) ')

returns 12,443 rows.

None of the columns used in the query are nullable, the only way a null could be introduced...

Grant Fritchey (3/31/2009)

---

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

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

Ken McKelvey (3/31/2009)

---

COALESCE can be used as a general query hint. (ie Non tsql specific.)

You should look a the query plans to see what is going on.

In this case putting...

Actually, I'm not so sure now that the coalesce operator does improve things. I've included the query and table information below but the issue is that the ([t0].[ProductType] = 1)...

Hi Mike,

Thanks for responding. Unfortunately that didn't seem to do it. I notice that there is an explicit check for the "-" character in the "AndExpression" case statement in ConvertQuery...

Hi Mike,

Great work! If I wanted to add the word "NOT" as an operator, would I just change

ExcludeOperator.Rule = Symbol("-")

to

ExcludeOperator.Rule = Symbol("-") Or Symbol("not")

Thanks

James

Hi Michael,

The LeftExtent/RightExtent is from Joe Celko's Nested Set pattern (http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html) which allows fast searching of leafs and nodes in a hierarchy.

I take your point about the Full...

Thanks for the reply. OK, so for table structures we have CAT_Product which is the product record, CAT_CatalogueSection which is the hierarchy table (with LeftExtent and RightExtent columns to make...