SQL Server 2008 iFTS allows you to efficiently search character data stored in char, nchar, and nvarchar columns. It can also be used to search binary content in varbinary columns, and XML data in xml datatype columns.
Before you can take advantage of iFTS’s powerful search capabilities, you need to create a full-text catalog and one or more full-text indexes. You can create these either through SQL Server Management Studio or by using Transact-SQL (T-SQL) statements.
Using the GUI to Create a Full-Text Catalog and Index
Using Management Studio, you can easily create full-text catalogs and full-text indexes. Here, we’ll walk through creating a full-text catalog for the AdventureWorks database and a full-text index on the Production.ProductModel table.
Creating a Full-Text Catalog
To create the full-text catalog, in Object Explorer, expand the Storage node under the target database, right-click the Full Text Catalogs node, and select New Full-Text Catalog from the context menu, as shown in Figure.
Choosing to create a new full-text catalog
You’ll be presented with the New Full-Text Catalog dialog box. In this dialog box, you select a name for your new full-text catalog, the full-text catalog owner, an accent-sensitivity setting, and whether this full-text catalog will be designated your default full-text catalog. The accent-sensitivity setting determines whether or not iFTS differentiates between characters with and without accent marks. Figure shows the entries for creating a catalog named AdvFTCatalog for this example.
Creating a new full-text catalog
Once you’ve created your full-text catalog, it’s time to define one or more full-text indexes on tables in your database.
Creating a Full-Text Index
Management Studio includes a Full-Text Indexing Wizard to guide you through creating a full-text index. Follow these steps to use the wizard:
Choosing to define a full-text index
Starting the Full-Text Indexing Wizard
Selecting an index in the Full-Text Indexing Wizard
Selecting columns for a full-text index
Selecting automatic change tracking for the full-text index
Assigning a full-text index to a full-text catalog
Optionally, you can set a full-text index population schedule.
Full-Text Indexing Wizard summary screen
After you click Finish in the Full-Text Indexing Wizard, a progress screen is displayed, showing the success or failure of the full-text index creation process. Figure shows that the full-text index in the example was successfully created.
Successful full-text index creation
Using T-SQL to Create a Full-Text Catalog and Index
It’s often necessary to script full-text catalog and full-text index creation. There are a number of reasons to script your full-text catalog and index creation, including the following:
To create the same full-text index as in the example in the previous section, use the CREATE FULLTEXT CATALOG statement:CREATE FULLTEXT CATALOG AdvFTCatalog
The CREATE FULLTEXT CATALOG statement specifies the full-text catalog name, which must be unique among full-text catalog names in the current database. This example specifies that the fulltext catalog will be created on the PRIMARY filegroup, accent sensitivity will be turned on, and the owner is dbo (via the AUTHORIZATION clause). We create the full-text catalog on the PRIMARY filegroup, since the AdventureWorks database has only a single filegroup by default. In production environments, Microsoft recommends creating an additional filegroup just for full-text catalogs.
After you create the full-text catalog with T-SQL you need to create one or more full-text indexes on it. The CREATE FULLTEXT INDEX statement fills this need:CREATE FULLTEXT INDEX ON Production.ProductModel
As in the Full-Text Indexing Wizard example in the previous section, this CREATE FULLTEXT INDEX statement creates a full-text index on the Name, CatalogDescription, and Instructions columns of the Production.ProductModel table. The single-column unique index used by the fulltext index is specified in the mandatory KEY INDEX clause. As in the previous example, we’ve used the integer primary key of the table, PK_ Product Model_ Product ModelID.
Each column in the example has the optional LANGUAGE identifier included, specifying a locale identifier (LCID) of 1033, which indicates US English. We’ve also specified automatic change tracking and usage of the default system stoplist.
The ALTER FULLTEXT INDEX statement enables the full-text index we just created on the Production.ProductModel table. Additional full-text index management functionality is exposed by the ALTER FULLTEXT INDEX and DROP FULLTEXT INDEX statements. You can use ALTER FULLTEXT INDEX to enable or disable a full-text index; set the change-tracking mode for a full-text index; change the stoplist used by a full-text index; and start, stop, pause, or resume a full-text index population. Thefollowing code starts a full population of the Production.ProductModel table’s full-text index.ALTER FULLTEXT INDEX ON Production.ProductModel
One you’ve created your full-text catalog and full-text indexes, you can take advantage of them with SQL Server’s full-text search predicates and functions.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.