Full-Text Indexing in SQL Server 2008

9/21/2010 4:24:43 PM
Full-text indexing is a feature of SQL Server 2008 that allows you to carry out sophisticated searches of text-based data called a full-text search. A full-text search is different from a normal search of data through a normal index because it enables you to use linguistic-based searches. For example, you could search a text-based column for inflectional forms of the word run, which would return results including running and ran. You could also search for similar words using the thesaurus feature, so searching for bicycle might return results including bike, pushbike, tandem and tricycle.

For the exam you need to focus on configuring and managing full-text indexes to support full-text searches rather than how to implement the searches themselves.

Configuring Full-Text Indexing

All databases in SQL Server 2008 are enabled for full-text indexing by default, so the first step you need to make is to create a full-text catalog, which is a logical object for grouping together full-text indexes. Microsoft recommends that full-text indexes with similar update activity patterns are grouped together in a full-text catalog, so that population schedules can be applied at the catalog level to reduce resource usage during population.

Full-text catalogs can be created from the right-click menu in SQL Server Management Studio at <instance> | Databases | <database> | Storage | Full Text Catalogs or by executing the following T-SQL:


Before you can create a full-text index for a table, you’ll need to make sure it has an existing unique, single column, nonnullable index. The full-text index will base its index keys on this.

Once you’ve fulfilled all the requirements you can create a full-text index using the CREATE FULLTEXT INDEX t-sql command, or use Management Studio by navigating to the table and selecting Full-Text Indexes from the right-click menu.

Managing Full-Text Indexes

Once you’ve created a full-text index, the process of filling it is referred to as populating the index. This is done initially when you create it, and by default the index will stay up to date as the underlying data changes. There are scenarios, however, where this default behavior is undesirable. As the population process is resource intensive, if you have frequent updates to your underlying text data, it might be prohibitive for you to keep the index automatically updated.

In this scenario, you can modify the default behavior of change tracking, which is to automatically track changes and populate the index. You can configure it to manual, which specifies that changes will be tracked but not propagated until you run or schedule the ALTER FULLTEXT INDEX ON <tablename> START UPDATE POPULATION t-sql command or set it to off, in which case changes will not be tracked or propagated until you run a FULL or INCREMENTALtimestamp data type to be present on the underlying table. population. An incremental population will update the index with changed rows since the last population, but it requires a column with the

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone