Programming4us
         
 
 
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:

CREATE FULLTEXT CATALOG <name>

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
 
 
Most View
- Exchange Server 2010 : Availability Planning for Mailbox Servers (part 4) - DAG Networks
- BizTalk 2009 : Using XML Namespaces (part 2) - Using Port Filters and Content-Based Routing
- BizTalk Server 2009 : The core principles of a service-oriented architecture (part 2)
- Keyword Research Tools (part 6)
- Microsoft XNA Game Studio 3.0 : Adding Bread to Your Game (part 1) - Using a Structure to Hold Sprite Information, Using the Gamepad Thumbsticks to Control Movement
- Microsoft Exchange Server 2003: Configuring Recipient Objects (part 4) - Configuring Storage Limits for Individual Mailboxes
- Writing Your First Phone Application - Adding Code (part 2)
- SQL Server 2008 R2 : Space Allocation Structures
- Windows Help Desk (Part 2) - AppCleaner backup, Moving partitions to resize them
- Windows 7 : Using Volume Activation (part 2) - Volume Activation Scenarios
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS