Return a random 5 records from full text index

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Simon Sabin - 23 May 2006 23:31 GMT

Bit of a teaser how can you achieve this.

Assume a search criteria i.e SQL AND Server
I want to not return the top 5 records but a random 5 records.

You can add anything to the indexed column and you can add anything to the
search, but the random results need to be out of the full results set of the
criteria passed in. The reason I want to do this is to avoid returning the
full resultset to SQL and do the filter there.

Cheers

Signature

Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons

Reply to this Message

Lubdha - 23 May 2006 23:58 GMT

Assume the table Table with PK TableID has an FT index.

SELECT TOP 5 *
FROM Table T
INNER JOIN CONTAINSTABLE(Table, *, ' "SQL" AND "Server" ') AS FT
ON T.TableID = FT.[KEY]
ORDER BY NEWID()

Would that work?

Reply to this Message

Simon Sabin - 24 May 2006 07:50 GMT

Thats exactly want I don't want to do. If "SQL" AND "Server" matches
100,000 records, full text has to return all these records from the full
text engine to the SQL engine before the query will return 5 records.

Signature

Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons

> Assume the table Table with PK TableID has an FT index.
>
[quoted text clipped - 5 lines]
>
> Would that work?

Reply to this Message

coosa - 26 May 2006 14:23 GMT

How about the new RANK functions? a mixture of rank with CLR? i mean a
simple CLR function could also do i guess! right?

Reply to this Message

Hilary Cotter - 24 May 2006 02:11 GMT

You might want to partition the table on the fly using indexed views and
full-text index them. Depending on the selectivity of some of the other
columns you create the indexed view on you might be able to get smaller
results set.

Another option is to add a clause to your query, i.e. select * from
containstable(tablename, columnname, '"SARG1" and "SARG2"), but you have to
know in advance what SARG2 will be and how frequently it occurs in your
content.

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Bit of a teaser how can you achieve this.
>
[quoted text clipped - 7 lines]
>
> Cheers

Reply to this Message

Simon Sabin - 24 May 2006 08:44 GMT

Indexed view is not an option

The challenge is determining what extra SARG to use. It would be great if
the ranking of a partial patch got different ranks.

i.e. matching "RND123*" OR "RND12*" OR "RND1*", RND123 would get a better
rank than RND1 because the former matches all three. With my testing the
ranking doesnt work.

Signature

Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons

> You might want to partition the table on the fly using indexed views and
> full-text index them. Depending on the selectivity of some of the other
[quoted text clipped - 17 lines]
>>
>> Cheers

Reply to this Message