Sql Server Full-Text Search Protips Part 1: Setup

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 binary fields with the proper setup considerations. Needless to say, there are a few protips to making Full-Text indexes work and taking advantage of them. In this post, I will tell you how to get Full-Tex

Getting Started

First, you are going to need a copy of Sql Server 2000 or 2005 Standard edition. MSDE or Sql Express do not have Full-Text capabilities. In order to enable Full-Text search, you must do a few things.

  • Enable Full-Text search on the database.
  • Create a Full-Text catalog.
  • Enable Full-Text searching on specific columns in your data.

To do this, we can run some system stored procedures. I should note that these are depereciated in 2005 in favor of DDL statements, but they still work and are a bit simpler to digest. Here is an example of the SQL necessary to create a Full-Text index in a database:

  exec sp_fulltext_database 'enable' exec sp_fulltext_catalog 'Catalog_Name', 'create' 

Table Design Considerations

Once there is a Full-Text index in place, you can start indexing columns. But lets not get ahead of ourselves. First and foremost, using fulltext indexing requires a single unique key on the table. Second, for any table where you are using Full-Text indexes, you really should have a TIMESTAMP column. This is a small binary column that is automatically updated whenever the row is changed. It is necessary as it acts as a flag for the indexing engine to allow it to incrementally index the table.

There is one other considerations for using Full-Text indexing on binary fields, such as word documents stored in a database. And that consideration is you must have a text field with the file extension (‘doc’ for word, for example). This is because the indexing needs to know what kind of file it is dealing with in order to parse the file and find the data. Out of the box, Sql Server can index text files, html files and Word documents. But it can be extended using IFilters. For example, Adobe provides a PDF IFilter for your use.

Enabling Full-Text Indexing on a Table & Columns

Lets say we have a rather simple table we wish to index, document_attachments. It has six fields:

  • Id (INT IDENTITY NOT NULL, primary key)
  • FileName (varchar(255) NOT NULL)
  • FileDescription(varchar(1000) NULL)
  • FileExtension (varchar(10) NOT NULL)
  • SaveStamp (TIMESTAMP)
  • FileData (IMAGE)

In order to allow for fulltext queries on your table, you must first enable the table for fulltext indexing, then enable fulltext indicies on specific columns. If we wished to create a fulltext index on the FileDescription & FileData fields, we would use the following T-SQL:

  exec sp_fulltext_table @tabname='document_attachments', @action='create', @ftcat=[CatalogName], @keyname=[Name of Primary Key index] exec sp_fulltext_column @tabname='document_attachments', @colname='FileName', @action='add' exec sp_fulltext_column @tabname='document_attachments', @colname='FileData', @action='add', @type_colname='FileExtension' 

Indexing Considerations

Now, before you use any fulltext catalog, it must be indexed. There are two separate scenarios here. For “normal fields”, that is fields that are not IMAGE or TEXT, Sql Server is capable of automatically tracking changes to columns for you. And, if you are working with Sql 2005, you really should be using VARCHAR(MAX) and VARBINARY(MAX), so this will not be a problem for some. But those of us still working on Sql 2000 will be required to setup an indexing schedule on any of the old school long text or binary columns in order to keep things fresh. Exactly how often to index depends on the application and your requirements. Do remember it is a bit of an expensive process, so you do not want to fire it off every 15 seconds to keep things “fresh.”

In any case, to enable change tracking (automated indexing) on a table, use the following T-SQL:

  exec sp_fulltext_table 'document_attachments', 'start_change_tracking' 

As for setting up the schedule, the best way is to fire up enterprise manager, find your Full-Text catalog, right click on it and choose Schedules. Then setup whatever schedule you feel appropriate.

But Wait Wyatt! Isn’t there more!

Why yes, there is more. Stay tuned for the next installment of Sql Server Full-Text Protips: Using Full-Text Queries for Fun and Profit.

Thanks and kick it if you like it.


Category: asp.net Time: 2006-11-12 Views: 1

Related post

  • 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 Protips Part 3: Getting RANKed 2006-12-30

    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 i

  • 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


Front-end development


development tools

Open Platform

Javascript development

.NET development

cloud computing


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

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