Examples

SELECT ProdReviewID, Prod_name, ProdReviewComments
from Productin.ProductReview
WHERE CONTAINS (ProdReviewComments, ' "awful"
OR "terrible" OR "rip-off");

Question: 5/16

What would you do to ensure optimal performance of above query?

Answer:
Create a full-text index on the ProdReviewComments column.


The commands to create full-text indexes are now symmetrical with the statements you would use to create any other index (clustered, non-clustered, and unique). They are CREATE FULLTEXT INDEX, ALTER FULLTEXT INDEX, and DROP FULLTEXT INDEX. The first part of the syntax (CREATE FULLTEXT INDEX ON TableName) is used to specify the table or the indexed view that you are creating your full-text index on. Here is a simple minimalistic example illustrating this:
CREATE TABLE MyTable
(PK INT NOT NULL IDENTITY(1,1) CONSTRAINT primarykey PRIMARY KEY, charcol char(200), charcol1 char(200))
CREATE FULLTEXT INDEX on MyTable
(Charcol) KEY index primarykey
The above example will only work if you already have a default full-text catalog defined for this database. Otherwise you will get the error message:
Msg 7623, Level 16, State 1, Line 1
A default full-text catalog does not exist in database 'FullText' or user does not have permission to perform this action.
If you did not have a default full-text catalog defined your syntax would have to look like this:
CREATE FULLTEXT INDEX on MyTable
(Charcol) KEY index primarykey ON catalog_name
You can full-text index multiple columns at once like this:
DROP FULLTEXT INDEX ON –-dropping the existing full-text index
GO
CREATE FULLTEXT INDEX on MyTable
(Charcol, Charcol1) KEY index primarykey ON catalog_name
Or you could alter the existing index like this:
ALTER FULLTEXT INDEX ON MyTable
ADD (charcol1)
It is possible to create full-text indexes on indexed views. This is very helpful when you need to partition your data to ensure that all results matching a limited query are returned. Here is an example (courtesy of David Poole of Microsoft) of creating a full-text index on an indexed view.
CREATE TABLE t1(id INT identity, ftcol NVarChar(100))
GO
INSERT t1(ftcol) VALUES ('hello')
INSERT t1(ftcol) VALUES ('world')
INSERT t1(ftcol) VALUES ('hello again')
GO
CREATE fulltext catalog cat1
GO
-- now create the view, must be schemabound view so it must explicitly
--specify columns and 2 part name for table
CREATE VIEW myview WITH SCHEMABINDING AS SELECT id, ftcol FROM dbo.t1 WHERE
id<=2
GO
-- create the index on the view to be used as full text key index
CREATE UNIQUE CLUSTERED INDEX idx ON myview(id)
GO
CREATE fulltext INDEX ON myview(ftcol) KEY INDEX idx ON cat1
GO
WHILE fulltextcatalogproperty('cat1','populatestatus') <> 0
BEGIN
WAITFOR DELAY '00:00:01'
END
GO
-- this will only hit row 1 as row 3 does not fall into the view definition
SELECT * FROM myview WHERE CONTAINS(ftcol,'hello')
GO

Language

In the above cases we will be assuming all the content will be indexed using the default language type for our server. You can determine the default language type by running the following command:
Sp_configure 'show advanced options',1
reconfigure
sp_configure 'default full-text language'
GO
Or you could set it, by using:
sp_configure 'default full-text language',1036
reconfigure
GO
In this example we are setting our default full-text language to French. Here is an example of this:
sp_configure 'default full-text language',0x40C
reconfigure
GO
To correlate the Language Code IDentifier (LCID) with the language you are using, query syslanguages:
SELECT alias, LCID FROM sys.syslanguages
If you wish to indicate that the contents of a column should be indexed by a different language than the default full-text language you can specify this in your full-text index creation statement. Here is an example of this.
CREATE FULLTEXT INDEX on MyTable
(Charcol LANGUAGE 1041) –-Specifying that your content is in Japanese

Performance

The performance considerations to be made when creating your full-text indexes using TSQL are identical as the considerations when creating your indexes through the GUI.