Sql Server Full-Text Search Protips Part 3: Getting RANKed

In Part 1 of this series we explored the methodology for enabling a Sql Server database for Full-Text Indexing and enabling Full-Text indices on specific tables. And in Part 2 of this series we explored the methodology for accessing these Full-Text indices using the CONTAINS and FREETEXT functions. In this final part of the series we will examine the usage of the CONTAINSTABLE and FREETEXTTABLE functions to provide ranked search results to the calling application.

As previously mentioned, the CONTAINSTABLE and FREETEXTTABLE functions query the selected Full-Text index for the provided search terms. The difference between these functions and their non-TABLE cousins—that is CONTAINS and FREETEXT—is that these functions take slightly different parameters and, most important, they return a table of values rather than a simple true/false answer.

These table functions take a slightly different set of parameters than their cousins. These parameters are:

  1. Free-Text indexed table to query.
  2. Columns within the table to query.
  3. Search term. For FREETEXTTABLE, this would generally just be the term(s) you wish to find. For CONTAINSTABLE, this term could be anything like the ones described in Part 2.
  4. Top_N_Results. An optional integer to set a limit to the number of results to get. This field is very important—limiting to the amount of results you actually wish to work with greatly lessens load on the database server, especially when dealing with large result sets.

The table these functions return contains two columns:

  1. KEY: this contains the primary key of the row in the indexed table. Obviously, but importantly, its data type is the same as the primary key in that table.
  2. RANK: this is a positive integer representing the relative strength of the match to your search term. This number is not an absolute value—it does not start at some fixed value and work down to near zero. The specific value is only relative compared to other results from that Free-Text query. Comparing the RANK value across different Full-Text queries does not necessarily get meaningful results.

Now, that two-field table might sound very simple, but with it you can do a lot of very fancy tricks using a very simple technique—just INNER JOIN the table returned from your Free-Text function on the KEY field to your desired results, then sort by the RANK column for sorting effects. Or even get those neat percentage matches you see on some site search engines.

For the following examples, we will be using the Microsoft Pubs database, like we setup in Part 2 of this series.

First, let’s examine getting ranked search results from using FREETEXTTABLE:

  SELECT      ftt.RANK,   titles.title_id,    titles.title FROM Titles INNER JOIN  FREETEXTTABLE(titles, notes, 'recipe cuisine') as ftt ON ftt.[KEY]=titles.title_id ORDER BY ftt.RANK DESC 

Nothing too tricky there, as you can see—just a simple INNER JOIN to the FREETEXTTABLE and ORDER BY the FREETEXTTABLE’s rank.

Now, to get percentages, you need to get a little fancier. For a match percentage, you need to divide the RANK by the top RANK value. You cannot, unfortunately, do this with just one Free-Text query; it requires selecting the TOP value then selecting the desired results and creating a calculated field for the percentages. Confused yet? Well, let’s see it in SQL.

  DECLARE @topRank int  set @topRank=(SELECT MAX(RANK) FROM FREETEXTTABLE(titles, notes, 'recipe cuisine', 1))  SELECT      ftt.RANK,   (CAST(ftt.RANK as DECIMAL)/@topRank) as matchpercent,   titles.title_id,    titles.title FROM Titles INNER JOIN  FREETEXTTABLE(titles, notes, 'recipe cuisine') as ftt ON ftt.[KEY]=titles.title_id ORDER BY ftt.RANK DESC 

One note—I very intentionally used the optional Top_N_Results parameter on the first FREETEXTTABLE call to limit the number of results returned. The reason for this is to make the query a lot less expensive on the database server.

One final key trick: as I mentioned in Part 2, one can get very fancy with CONTAINS and search terms. This really, really applies to CONTAINSTABLE. For example, using weighted search terms plus the RANK column lets one get the right results for end users. For example, let’s say we wanted to find books on recipies or cuisies, but especially recipies:

  SELECT   ftt.RANK,   titles.title_id,    titles.title,   notes FROM Titles INNER JOIN  CONTAINSTABLE     (   titles,     notes,      'ISABOUT("recipe*" weight (.7), "cuisine*" weight (.2))'    )  as ftt ON ftt.[KEY]=titles.title_id ORDER BY ftt.RANK DESC 

One thing to note in the above example is that, because we are using a CONTAINS-type query, the search term (ISABOUT . . .) needed to include other forms to catch plurals and such. In this cases I used the “SearchTerm*” construct to tell the Full-Text engine to find all terms starting with ‘SearchTerm’.

Finally, to review:

  1. In Part 1 we examined how to create Full-Text indices and considerations for maintaining those indexes.
  2. In Part 2 we examined how to query your Full-Text indices using the CONTAINS and FREETEXT predicates, and the difference between those functions.
  3. In this part, we looked at using the CONTAINSTABLE and FREETEXTTABLE functions to get ranked search results from your Free-Text indices. In addition, we examined some limitations of and workarounds for using Free-Text queries.

I hope you all have enjoyed, and please do post any comments or questions. And finally, feel free to kick it if you like it.

Replay

Category: asp.net Time: 2006-12-30 Views: 1
Tags:

Related post

  • Sql Server Full-Text Search Protips Part 1: Setup 2006-11-12

    As I have previously mentioned, Sql Server's Full-Text search can let a developer create some very slick features disturbingly easily. And, unlike many other Full-Text implementations, it is not limited to plain text fields. It can also search within

  • Sql Server Full-Text Search Protips Part 2: CONTAINS vs. FREETEXT 2006-12-06

    In Part 1 of this series we explored the methodology for enabling a Sql Server database for Full-Text Indexing and enabling Full-Text indicies on specific tables. But now we get to the good part-how to use that index in your application. There are fo

  • sql server full text search - fuzzy searching? 2013-04-06

    Is there a way to configure fuzzy searches in sql server full text search. Meaning if I search for a term called POWDER, I must get matches (i.e. strings) which contain any variations of it within an allowable distance, like for e.g. the matches can

  • SQL Server Full-Text Search: combining proximity term and thesaurus 2013-11-26

    Scenario I am developing a full-text search over a products table. The full-text indexed field of th table contains a brief description of the product, the name of the category and the product code. I succeed in querying the table using the CONTAINS

  • SQL Server Full Text Search resource consumption 2011-01-09

    When SQL Server builds a fulltext index computer resources are consumed (IO/Memory/CPU) Similarly when you perform full text searches, resources are consumed. How can I get a gauge over a 24 hour period of the exact amount of CPU and IO(reads/writes)

  • viewing the population? (sql server full text search) 2013-04-16

    I'm thinking that when we are working on full text search, we might want to view how sql server FT engine breaks the data and indexes it. For my case the column being indexed are simple nvarchar strings with spaces (no punctuation). I'd want to be ab

  • What are safe characters to use in SQL Server Full Text Search? 2013-06-21

    I've been playing with full text search on SQL Server 2012. I must say my attempts to target special characters in search queries were futile - SS does not understand them. What's more, sometimes the presence of special characters breaks the query en

  • Sql server full text search performance with additional conditions 2013-09-23

    We have a performance problem with SQL Server (2008 R2) Full text search. When we have additional where conditions to full-text search condition, it gets too slow. Here is my simplified query: SELECT * FROM Calls C WHERE (C.CallTime BETWEEN '2013-08-

  • SQL Server Full Text Search - .rtf files incorrectly indexed by rtf tags 2014-01-23

    I've set up a full-text index on my SQL Server DB on a varbinary(max) column. I have a type column specified, which contains the extension of the file, such a ".doc", ".pdf" etc. However, I have noticed that when any .rtf files are ind

  • SQL Server Full Text Search Engine is not returning consistent results 2015-03-24

    I'm working on an application that makes use of the Full Text Search Engine from SQL Server. In order to convert the user input into readable parameters for SQL, i'm using This very useful FTS query converter. Now, the issue at hand: We have a record

  • SQL-Server full-text search does not use thesaurus in CONTAINSTABLE unless the FORMSOF is used 2015-09-18

    I am trying to improve the full-text search capabilities of system. I have created few synonyms in SQL Server Thesaurus according to MSDN article. <expansion> <sub>running</sub> <sub>jogging</sub> </expansion> Now I can

  • Sql Server Full Text Search one table for text values in another table 2012-03-07

    Is it possible to run a full text search on an FTS-indexed column for all values in another table? Conceptually it'd look like select d.* from Docs d, Tags t where CONTAINS(d.fulltext, t.tagvalue) I'm rather new to MSSQL FTS, though I know that the e

  • SQL Server full text search query takes way too long to execute 2015-07-13

    I've got query snippet as follows: SELECT * FROM CONTAINSTABLE(dbo.Analysis, *, 'FORMSOF(FREETEXT, "historic year-on-year exchange rates forecast year-on-year exchange rates")'); I'm running it on SQL Server 2014. However it executes forever, so

  • How can I return the full text of a document indexed in SQL Server Full-Text? 2015-07-24

    I'm using SQL Server 2014 with FileTables to store a large number of documents in different formats. The iFilters are working great, and everything is getting indexed with FTS + Semantic Search. Now I'd like to run some additional processing on the t

  • SQL Server Full-Text Indexer with stoplists/stopwords 2012-12-06

    As I'm working on a graduate project (Textmining with SQL Server 2012 Semantic Search) I run into a situation where I need to post a question on this website, hoping someone can help me. This question is about stoplists and stopwords in SQL Server 20

  • How much data can SQL Server full text indexing handle? 2013-05-30

    I realize that the question is vague and it depends on hardware and our needs. We currently have 5 million rows of data, a total of 5GB of data which we want to index using full text indexing. Our data increases quite rapidly and it's not unreasonabl

  • SQL Server full text indexing disabled 2013-09-30

    I have a SQL Server 2008 R2 server instance where I'm unable to use full text catalogs. It was upgraded from SQL Server 2008. When I go to the database properties of an existing database or try to create a new database the "Use full-text indexing&quo

  • SQL Server Full-Text Index with Stoplist 2014-01-28

    I've searched this site and the internet in general and can't find any information which answers my question, so I thought I'd post it here. I have Full Text search operating on a database in SQL server 2008 and I have 2 questions relating to the ind

  • SQL Server Full txt Search - Thesaurus Entries 2015-03-30

    I'm thinking of adding a full text search to our product (currently using SQL 2005 Enterprise), so just doing some preliminary research. I read that you can add thesaurus entries, so "Internet Explorer" would also have related entries such as &q

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 2.169 (s). 13 q(s)