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
- Active Directory Domain Services 2008: Enable the Directory Service Replication Auditing Subcategory
- Exchange Server 2010 : Upgrading from and Coexisting with Exchange Server 2007 (part 2) - Upgrading Message Connectivity From Exchange Server 2007
- Exchange Server 2010 : Planning for Messaging Security
- jQuery 1.3 : Headline rotator (part 3) - Setting up the rotator
- Windows Server 2008 : Perform an Unscheduled Full Server Backup of a Domain Controller by Using the Windows Interface
- Sharepoint 2010 : Excel Service - Demonstration Scenario (part 1)
- Exchange Server 2010 : Manage Access for Mobile Devices (part 3) - Protect Mobile Devices
- SQL Server 2008: Administering Database Objects - Working with Database Snapshots
- SharePoint 2010 : Using Enterprise Search (part 2) - Administering Content Sources
- Windows Vista : User Account Control
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