Expand News
IS Administration
Expand MMC
Expand Com Objects
Expand IDA/IDQ
IS Programming
Expand IS Programming
IS TroubleShooting
Expand Indexing
Expand Querying
IS Optimizing
Expand Querying
Expand Indexing
IS Links
Expand MSDN
Expand TechNet
Expand Other MS
Expand Other Links
iFilters
Expand iFilters
SQL FTS Programming
Expand Loading Data
Expand Indexing
Expand Querying
Contains
Freetext
ContainsTable/FreetextTable
ASP
ISAPI
.Net
SQLXML
Thesaurus
Languages
Trouble Shooting
Hit Highlighting
Blobs
Dealing with Noise words
Expand SQL FTS Links
SQL 2005 FTS
Expand What's New

Index Server Faq

CONTAINSTABLE and FREETEXTTABLE

Normally, you should never use a bare CONTAINS or FREETEXT predicate in your search, as you really have no way of limiting how many rows are returned your results set. This has two implications: First, you may get more results than you need, which will degrade performance on your system; and second, the results are not returned in any particular order. CONTAINSTABLE and FREETEXTTABLE provide you with a rank assigned to each term and allow you to use the top operator to only return the top n hits, but you have to join the query returned from CONTAINSTABLE and FREETEXTTABLE with your original table. This is because CONTAINSTABLE and FREETEXTTABLE only return the unique index value (typically your PK), and the rank.

Typical usage of the CONTAINSTABLE and FREETEXTTABLE:

SELECT * FROM table_name AS FT_TBL INNER JOIN

CONTAINSTABLE(table_name, column_name, 'search_term') AS KEY_TBL

ON FT_TBL.PK = KEY_TBL.[KEY]

or

SELECT * FROM table_name AS FT_TBL INNER JOIN

FREETEXT(table_name, column_name, 'search_term') AS KEY_TBL

ON FT_TBL.PK = KEY_TBL.[KEY]

where PK is your Full‑Text Index key and normally your primary key.

Using the top operator:

SELECT * FROM table_name AS FT_TBL INNER JOIN

CONTAINSTABLE(table_name, column_name, 'search_term',100) AS KEY_TBL

ON FT_TBL.PK = KEY_TBL.[KEY]

or

SELECT * FROM table_name AS FT_TBL INNER JOIN

FREETEXTTABLE(table_name, column_name, 'search_term',100) AS KEY_TBL

ON FT_TBL.PK = KEY_TBL.[KEY]

Rank will be the last column returned in the above two queries. The "100" returns the top 100 matching rows ordered by highest rank first.

If you need to specify that rank you can return it as follows:

SELECT table_name.col1, table_name.col2, table_name.col3, KEY_TBL.RANK FROM

table_name AS FT_TBL INNER JOIN FREETEXTTABLE(table_name, column_name, 'search_term',100) AS KEY_TBL

ON FT_TBL.PK = KEY_TBL.[KEY]

Ranking for both predicates varies for several reasons. First, CONTAINSTABLE is matching to the exact phrase, whereas FREETEXTTABLE is doing word stemming. FREETEXTTABLE has built in nearness and CONTAINS does not.

Second, FREETEXTTABLE will return rows factoring in nearness for each search term until the rank is 0. The closer the individual terms are to each other, the higher the rank; the further apart the terms are, the lower the rank.

If you return the rank, you will see that it is a value between 0 and 1000; 1000 indicates a perfect match (sort of like my wife and me), but the rank is derived from using a statistical method that attempts to assign a value to relevance. Please refer to Chapter 1 for a more detailed discussion of the statistical derivation of rank.

The larger the number of unique keys in your catalog, the higher your value of rank will be for an exact match. Also, the larger your rows are, the more likely you will have higher rankings.

Note that you can only return the rank for CONTAINSTABLE and FREETEXTTABLE.

The larger your results set, the more CPU your system needs to return the results set. It is a good practice to limit your result set to the lowest value possible. Ideally this would be 1, but in practice, a good value is 100. The only possible case where you might not want to limit your results set to a predefined value would be for a job search Web site, where your customers might want to see all of the jobs and not just the top 100. Chances are also good that they would go through every one of the hits returned as well.

Before we leave this section lets have a look at a typical stored procedure you would use to return results:

Code Sample Stored procedure illustrating the usage of CONTAINSTABLE with ranking and ordering.

CREATE proc search @search char(20), @return int output

as

declare @strSearch as char(500)

declare @30daysago datetime

declare @today datetime

set @today=getdate()

set @30daysago=getdate()-30

set @strSearch='select description, inventory, hyperlink, characterization from ProductDetail as PD, CONTAINSTABLE(ProductDetail, description, '+char(39) +char(34)

select @strSearch=rtrim(@strSearch) + char(39) +rtrim(@search) +char(34)+char(39)

select @strSearch=rtrim(@strSearch)+ ', 30) as cont where PD.skuid=cont.[key] and inventory_Date> @30daysago and  inventory_Date < @today order by cont.rank desc,[ KEY'

--print @strSearch

exec sp_sqlexec @strSearch

IF (@@ERROR <> 0)

    return @@error

else

return 0

Here we are querying the ProductDetail table for rows which contain the search phrase, and were inventoried less than 30 days ago.  Key to this working is the fact we are ordering this table by rank descending, and limiting our row set to 30 rows.  Descending rank ensures that our results are ordered correctly before they are filtered through the join condition.

Typical usage is:

declare @strSearch char(50)

declare @output int

set @strSearch='white gold'

exec @output=search @strSearch, @output

if( @output= 0)

            print 'query successful'

else

            print 'query un-successful'

There error handling here is laughable, but in your calling application you would want to check the return value and report something more meaningful than query unsuccessful.  I prefer using the CONTAINSTABLE for performance.  However you will be plagued by the "Your query contains noise words" bugaboo.  Also double single quotes or double double quotes in your calling application. 

 

designed by :: smilla group