SQL Server

Configuring SQL Server 2008 : Full-Text Indexing

10/17/2010 5:45:29 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 timestamp data type to be present on the underlying table.

Other -----------------
- SQL Server 2008 : Working with Indexes
- SQL Server 2008 : Working with Constraints
- SQL Server 2008 : Working with Tables and Views
- SQL Server 2008 : Viewing and Modifying Data (part 3) - Creating Functions and Creating Triggers
- SQL Server 2008 : Viewing and Modifying Data (part 2) - Creating Stored Procedures
- SQL Server 2008 : Viewing and Modifying Data (part 1) - Creating Views
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- Sigma 24mm f/1.4 DG HSM Art

- Canon EF11-24mm f/4L USM

- Creative Sound Blaster Roar 2

- Alienware 17 - Dell's Alienware laptops

- Smartwatch : Wellograph

- Xiaomi Redmi 2
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8