Yes, something like
select top 10 Name, StandardCost
from Production.Product
where contains(Name, '"Mount*"')
order by ListPrice desc
does work, the problem is that the FTS will dig up all matching rows
first (and in my application there are usually a lot of hits) and then
SQL Server will sort there and return the top 10 rows.
With SQL Turbo my queries are a lot faster, since I can specify an
additional ordering index (which may also reside in a different table)
and sort by that index. The SQL Trubo fulltext engine will then only
return the top n rows that I have specified.
The containstable statement allows a similar functionality, like in
Hilary's example:
select top 10 Name, StandardCost from Production.Product
join (
select [key], rank from
containstable(Production.Product, Name, '"Mount*"', 1000)
) as k
on k.[key]= Production.Product.ProductID
order by ListPrice desc
The containstable statement will only return the first 1000 rows here,
but these will always be sorted by rank. I need this sorted be
ListPrice instead. If I do this:
select top 10 Name, StandardCost from Production.Product
join (
select [key], rank from
containstable(Production.Product, Name, '"Mount*"', 10)
) as k
on k.[key]= Production.Product.ProductID
order by ListPrice desc
I will not get the 10 matching rows with the highest ListPrice.
> DC wrote on 21 Aug 2006 03:17:50 -0700:
>
[quoted text clipped - 44 lines]
>
> Dan
Daniel Crichton - 23 Aug 2006 09:22 GMT
As Hilary pointed out, MS FTS doesn't work like this. Are you sure SQL Turbo
isn't supported? Quest Software are still listing it on their current
products page, and KB articles have been added within at least the past
month to their site. Have you tried contacting them? http://www.quest.com
Dan
DC wrote on 22 Aug 2006 09:00:10 -0700:
> Yes, something like
>
[quoted text clipped - 85 lines]
>>
>> Dan
DC - 24 Aug 2006 14:46 GMT
Our admins told me the product was sold twice and that they would not
get an updated version (and the current version has a couple of flaws),
but I will check again. I will also check if FTS does maybe meet our
performance requirements in the 2005 version although it digs up all
the FT matches.
Thank you, Hilary and Daniel!
> As Hilary pointed out, MS FTS doesn't work like this. Are you sure SQL Turbo
> isn't supported? Quest Software are still listing it on their current
[quoted text clipped - 94 lines]
> >>
> >> Dan