Creating Full-Text Catalogs

You can access the GUI full-text catalog wizard by right-clicking Full Text Catalogs in the SSMS Object Explorer. The New Full-Text Catalog option on the pop-up context menu starts the wizard (see Figure 10-2).

After selecting New Full-Text Catalog, SSMS presents the wizard’s New Full-Text Catalog window. This window allows you to define the name of your full-text catalog, the full-text catalog’s owner, an accent sensitivity setting, and whether or not this full-text catalog is designated as the default for a database. The New Full-Text Catalog window is shown in Figure 10-3.

For this sample full-text catalog, we chose the following options:

• The full-text catalog is named AdventureWorksFTCat, and dbo is designated as the owner.
• The first created full-text catalog is designated the default full-text catalog for the database. When a new full-text index is created you will have a choice to create it in the default catalog or in any additional non-default catalogs.
• The accent sensitivity is set to Insensitive, meaning that words with accent marks are treated as equivalent to those without accent marks (e.g., for search purposes, resumé is the same as resume).

You can also create and manage full-text catalogs using T-SQL statements. Listing 10-1 shows how to create the same full-text catalog that we created previously in this section with the SSMS wizard.

Listing 10-1. Creating a Full-Text Catalog with T-SQL

CREATE FULLTEXT CATALOG AdventureWorksFTCat
  WITH ACCENT_SENSITIVITY  =  OFF
  AS DEFAULT
  AUTHORIZATION dbo;

Once you’ve created your full-text catalog, the next step is to build full-text indexes. We describe full-text index creation in the next section. Maximum performance full-text catalogs, particularly those you anticipate will become very large, should be created on filegroups that are located on their own physical drives. This is also useful for administrative functions such as performing filegroup backups and restores independent of data and log files.

Creating Full-Text Indexes

As with full-text catalogs, you have two options for creating full-text indexes—you can use the GUI wizard in SSMS, or you can use T-SQL statements. Once you’ve created a full-text catalog, as described in the previous section, it’s time to define your full-text indexes. Begin by right-clicking a table; the example in Figure 10-4 uses the Production.ProductModel table, in the SSMS Object Explorer to pull up the table context menu. From the context menu, choose the Full-Text Index Define Full-Text Index option, shown in Figure 10-4.

The full-text index wizard shows a splash screen the first time you access it. You can choose to turn off the splash screen or just ignore it. On the next screen, shown in Figure 10-5, the wizard allows you to select a single-column unique index on the table. Every full-text index requires a single-column unique index that allows the full-text index to reference individual rows in the table. If you don’t have a single-column unique index defined on the table you’re trying to create a full-text index on, the wizard will display an error message as soon as you try to run it. In this example, we’ve chosen to use the table’s integer primary key for the full-text index.

After you select a unique index, you’ll choose the columns that will provide the searchable content for the full-text index. You can specify char, nchar, varchar, nvarchar, xml, varbinary, varbinary(max), and image columns in this step. In Figure 10-6, the nvarchar and xml data type columns of the table are selected to participate in the full-text index. We’ve also selected English as the word-breaker language for each of these columns. The word-breaker language specification determines the language used for word-breaking and stemming. SQL Server 2012 currently recognizes over 50 different languages.

After you’ve selected the columns that will participate in full-text searches against a table, you must select the change-tracking option. Change tracking determines whether SQL Server maintains a change log for the full-text indexed columns, and how the log is used to update the full-text index. Figure 10-7 shows the change-tracking options available through the wizard.

The change-tracking options available through the wizard include the following:

• Automatically: SQL Server updates the full-text index automatically when data is modified in the columns that participate in the full-text index. This is the default option.
• Manually: The change-tracking log is either used to update the full-text index via SQL Agent on a scheduled basis, or through manual intervention. This option is useful when automatic full-text index updates could slow down your server during business hours.
• Do not track changes: SQL Server does not track changes. Updating the full-text index requires you to issue an ALTER FULLTEXT INDEX statement with the START FULL or INCREMENTAL POPULATION clause to populate the entire full-text index.

The next step in the wizard allows you to assign your full-text index to a full-text catalog. You can choose a preexisting full-text catalog, like the AdventureWorksFTCat shown in Figure 10-8, or you can create a new full-text catalog. You can also choose a filegroup and full-text stoplist for the full-text index in this step.

The final steps of the wizard allow you to create a full-text index population schedule and review your previous wizard selections. Since automatic population is used in the example, no schedule is necessary.

In the review window of the wizard, shown in Figure 10-9, you can look at the choices you’ve made in each step of the wizard and go back to previous steps to make changes if necessary. Once you click the Finish button, the full-text index is created in your database.

The SSMS full-text index wizard is very thorough, but you can also create and manage full-text indexes using T-SQL statements. Listing 10-2 shows the T-SQL statements required to create and enable a full-text index with the same options previously selected in the SSMS wizard example.

Listing 10-2. Creating a Full-Text Index with T-SQL Statements

CREATE FULLTEXT INDEX
ON Production.ProductModel
(
  CatalogDescription LANGUAGE English,
  Instructions LANGUAGE English,
  Name LANGUAGE English
  )
KEY INDEX PK_ProductModel_ProductModelID
ON
(
  AdventureWorksFTCat
)
WITH
(
CHANGE_TRACKING AUTO
);
GO
ALTER FULLTEXT INDEX
ON Production.ProductModel ENABLE;
GO

The CREATE FULLTEXT INDEX statement builds the full-text index on the Production.ProductModel table with the specified options. In this example, the CatalogDescription, Instructions, and Name columns are all participating in the full-text index. The LANGUAGE clause specifies that the English language word breaker will be used to index the columns. A word breaker is a naturally occurring break between words based on a language’s lexicon. Setting the word breaker language to English helps FTS understand how the sentences are structured in order to better search on individual words. The KEY INDEX clause specifies the primary key of the table, PK_ProductModel_ProductModelID, as the single-column unique index for the table. Finally, the CHANGE TRACKING AUTO option turns on automatic change tracking for the full-text index.

The ALTER FULLTEXT INDEX statement in the listing enables the full-text index and starts a full population. ALTER FULLTEXT INDEX is a flexible statement that can be used to add columns to, or remove columns from, a full-text index. You can also use it to enable or disable a full-text index, set the change-tracking options, start or stop a full-text index population, or change full-text index stoplist settings.

The FREETEXT Predicate

The FREETEXT predicate offers the simplest method of using FTS to search character-based columns of a full-text index. FREETEXT searches for words that match inflectional forms and thesaurus expansions and replacements. The FREETEXT predicate accepts a column name or list of columns, a free-text search string, and an optional language identifier (a locale ID, or LCID). Because it is a predicate, FREETEXT can be used in the WHERE clause of a SELECT query or DML statement. All rows for which the FREETEXT predicate returns true (a match) are returned. Listing 10-3 shows a simple FREETEXT query that uses the full-text index created on the Production.ProductModel table in the previous section. The results are shown in Figure 10-10. The wildcard character (*) passed as a parameter to the FREETEXT predicate indicates that all columns participating in the full-text index should be searched for a match. The second FREETEXT parameter is the word you want to match.

Listing 10-3. Simple FREETEXT Full-Text Query

SELECT
   ProductModelID,
   Name,
   CatalogDescription,
   Instructions
FROM Production.ProductModel
WHERE FREETEXT(*, N'sock'),

The FREETEXT predicate automatically stems words to find inflectional forms. The query in Listing 10-3 returns rows that contain an inflectional form of the word sock—in this case, FTS finds two rows that contain the plural form of the word, socks. Notice that if you were to replace the word “socks” with “sox” you receive the same result set. This is because FREETEXT also performs FTS thesaurus expansions and replacements automatically, if a thesaurus file is available.

The integration of FTS with the SQL Server query engine results in a more efficient FTS experience. In SQL Server 2012, FTS can take advantage of optimized operators like the Table Valued Function [FulltextMatch] operator shown in Figure 10-11. The query plan shown is generated by the query in Listing 10-3.

Listing 10-4 demonstrates a FREETEXT query that retrieves all rows that contain inflectional forms of the word ride in the CatalogDescription column. Another word for this process is called stemming. Inflectional forms that are matched in this query include the plural noun riders and the verb riding. In this FREETEXT query, the CatalogDescription column name is identified by name to restrict the search to a single column, and the LANGUAGE specifier is used to indicate LCID 1033, which is US English. The results are shown in Figure 10-12.

Listing 10-4. FREETEXT Query with Automatic Word Stemming

SELECT
  ProductModelID,
  Name,
  CatalogDescription,
  Instructions
FROM Production.ProductModel
WHERE FREETEXT(CatalogDescription, N'weld', LANGUAGE 1033);

You can’t see the words that matched in the xml type CatalogDescription (there’s not enough space on the page to reproduce the entire result). Rest assured that FREETEXT has located valid matches in the row. For the first match the XML has the text “The heat treated welded aluminum,” while the second match has the text “it is welded and heat treated.”

The CONTAINS Predicate

In addition to the FREETEXT predicate, SQL Server 2012 supports the CONTAINS predicate. CONTAINS allows more advanced full-text query options than the FREETEXT predicate. Just like FREETEXT, the CONTAINS predicate accepts a column name or list of columns, a search condition, and an optional language identifier as parameters. The CONTAINS predicate can search for simple strings like FREETEXT, but it also allows sophisticated search conditions that include word or phrase prefixes, words that are in close proximity to other words, inflectional word forms, thesaurus synonyms, and combinations of search criteria.

The simplest CONTAINS predicates are basic word searches, similar to FREETEXT. Unlike FREETEXT, however, the CONTAINS predicate does not automatically search for inflectional forms of words or thesaurus expansions and replacements. Listing 10-5 modifies Listing 10-4 to demonstrate a simple CONTAINS query. The results are shown in Figure 10-13. As you can see, a couple of rows that do not contain an exact match for the word weld are eliminated from the results.

Listing 10-5. Simple CONTAINS Query

SELECT
  ProductModelID ,
  Name,
  CatalogDescription,
  Instructions
FROM Production.ProductModel
WHERE CONTAINS (*, N’weld’);

To use inflectional forms or thesaurus expansions and replacements with CONTAINS, use the FORMSOF generation term in your search condition. Listing 10-6 performs a CONTAINS search on the Name and CatalogDescription columns of the Production.ProductModel table. The results, which include matches for inflectional forms of the word sport, like sports and sporting, are shown in Figure 10-14.

Listing 10-6. Sample CONTAINS Query with FORMSOF Inflectional Generation Term

SELECT
  ProductModelID ,
  Name,
  CatalogDescription
FROM Production.ProductModel
WHERE CONTAINS
(
  (
    Name,
    CatalogDescription
  ),
  N'FORMSOF(INFLECTIONAL, sport)'
);

The CONTAINS predicate also allows you to combine simple search terms like these with the AND (&), AND NOT (&!), and OR (|) Boolean operators. Listing 10-7 demonstrates combining two search terms in a CONTAINS predicate. The results of this sample query, which retrieves all rows containing inflectional forms of the word sport (like sports) or the word tube in the Name or CatalogDescription columns, are shown in Figure 10-15.

Listing 10-7. Compound CONTAINS Search Term

SELECT
  ProductModelID ,
  Name,
  CatalogDescription
FROM Production.ProductModel
WHERE CONTAINS
(
  (
    Name,
    CatalogDescription
),
N'"tube" | FORMSOF (INFLECTIONAL, sport)'
);

Listing 10-7 uses FORMSOF to return matches for inflectional forms. You can also use the FORMSOF (THESAURUS,...) format to return matches for expansions and replacements of words, as defined in your language-specific thesaurus files.

CONTAINS also supports prefix searches using the wildcard asterisk (*) character. Place the search word or phrase, immediately followed by the wildcard character, in double quotes to specify a prefix search. Listing 10-8 demonstrates a simple prefix search to retrieve all rows that have a word starting with the prefix bot in the Name column. The results are shown in Figure 10-16.

Listing 10-8. CONTAINS Prefix Search

SELECT
  ProductModelID ,
  Name
FROM Production.ProductModel
WHERE CONTAINS (Name, N'"bot*"'),

The CONTAINS predicate also supports the NEAR (∼) keyword for proximity searches. NEAR will return matches for words that are close to one another in the source columns. Listing 10-9 demonstrates a NEAR proximity search that looks for instances of the word aluminum that occur in close proximity to the word jig in the Instructions column. The results are shown in Figure 10-17. This example is considered a generic proximity search.

Listing 10-9. CONTAINS Proximity Search

SELECT
  ProductModelID ,
  Name
FROM Production.ProductModel
WHERE CONTAINS (Instructions, N'aluminum NEAR jig'),

SQL Server 2012 introduces a custom proximity search for the NEAR clause. It allows you to easily search for words within a customizable distance from one another. It also allows you to define the order of the phrases in your search. The distance is determined by the number of non-searchable words between the words included in your search. If we take the example in Listing 10-9 and convert it to a custom proximity search, we find that in order to get the same results we have to include a distance of three. This means that a maximum of three words exist between the words aluminum and jig. Listing 10-10 shows the revised code.

Listing 10-10. CONTAINS Custom Search

SELECT
  ProductModelID ,
  Name
FROM Production.ProductModel
WHERE CONTAINS(Instructions, 'NEAR((aluminum,jig), 3)'),

Listing 10-10 gives you the same results as Figure 10-17. A distance of two will give you no results but any other number above three gives you the same results as the original. Keep in mind the distance between the words also includes stopwords. Remember stopwords are words usually not included in searches. Keep in mind too that the custom proximity clause is not limited to only two search words. You could have also included words like “bike,” “weld,” and “frame”—for example, NEAR((bike, weld, frame), 3). You can even include phrases like “bike riding” or “welding frame.” Whatever you choose, the distance is still based on the distance between the first and last word listed in the condition.

By default the custom proximity search will ignore the order of the search words. In the example above, jig could be within a distance of three either before or after the word aluminum. If you want to control the order of the search words then you need add the TRUE clause in the NEAR statement. Listing 10-11 shows two examples. The first has jig before aluminum and the second has aluminum before jig. Notice that only the second example returns values.

Listing 10-11. Custom Search with TRUE Clause

SELECT
  ProductModelID ,
  Name
FROM Production.ProductModel
WHERE CONTAINS(Instructions, 'NEAR((jig, aluminum),3, TRUE)'),
  
SELECT
  ProductModelID ,
  Name
FROM Production.ProductModel
WHERE CONTAINS(Instructions, 'NEAR((aluminum, jig),3, TRUE)'),

The custom proximity search also allows for search conditions which combine multiple grouping of words using expressions like AND, OR, and AND NOT. The added flexibility of the SQL Server 2012 custom proximity search adds advanced features not available in the generic search. Going forward, all searches should be done using the custom properties.

The FREETEXTTABLE and CONTAINSTABLE Functions

SQL Server provides TVF-based counterparts to the FREETEXT and CONTAINS predicates, known as FREETEXTTABLE and CONTAINSTABLE. These functions operate like the similarly named predicates, but both functions return result sets consisting of a table with two columns, named KEY and RANK. The KEY column contains the key index values relating back to the unique index of matching rows in the source table, and the RANK column contains relevance rankings.

The FREETEXTTABLE function accepts the name of the table to search, a single column name or column list, a search string, and an optional language identifier just like the FREETEXT predicate. FREETEXTTABLE can also take an additional “top n by rank” parameter to limit the rows returned to a specific number of the highest-ranked rows. The results of FREETEXTTABLE are useful for joining back to the source table via the KEY column of the results. Listing 10-12 demonstrates a simple FREETEXTTABLE query that locates rows where the word aluminum appears in the Instructions column of the Production.ProductModel table. The results are joined back to the source table to return the ProductModelID and Name, as shown in Figure 10-18.

Listing 10-12. FREETEXTTABLE Results Joined to Source Table

SELECT
  ftt.[KEY],
  ftt.[RANK],
  pm.ProductModelID ,
  pm.Name FROM FREETEXTTABLE
(
  Production.ProductModel,
  Instructions,
  N'aluminum'
) ftt
INNER JOIN Production.ProductModel pm
  ON ftt.[KEY]  =  pm.ProductModelID;

The CONTAINSTABLE function offers the advanced search capabilities of the CONTAINS predicate in a function form. The CONTAINSTABLE function accepts the name of the source table, a single column name or list of columns, and a CONTAINS-style search condition. Like FREETEXTTABLE, the CONTAINSTABLE function also accepts an optional language identifier and “top n by rank” parameter. Listing 10-13 demonstrates the CONTAINSTABLE function in a simple keyword search that retrieves KEY and RANK values for all rows containing inflectional forms of the word tours. The results are shown in Figure 10-19.

Listing 10-13. Simple CONTAINSTABLE Query

SELECT
  [KEY],
  [RANK]
FROM CONTAINSTABLE (
Production.ProductModel,
[Name],
N'FORMSOF(INFLECTIONAL, tours)'
);

CONTAINSTABLE supports all of the options supported by the CONTAINS predicate, including the ISABOUT term, which allows you to assign weights to the matched words it locates. With ISABOUT, you assign a weight value between 0.0 and 1.0 to each search word. CONTAINSTABLE applies the weight to the relevance rankings returned in the RANK column. Listing 10-14 shows two CONTAINSTABLE queries. The first query returns all products with the words aluminum or polish in their XML Instructions column. The second query uses ISABOUT to assign each of these words a weight between 0.0 and 1.0, which is then applied to the result RANK for each row. The results, shown in Figure 10-20, demonstrate how ISABOUT weights can rearrange the rankings of your CONTAINSTABLE query results.

Listing 10-14. ISABOUT in a CONTAINSTABLE Query

SELECT
  ct.[RANK],
  ct.[KEY],
  pm.[Name]
FROM CONTAINSTABLE
(
  Production.ProductModel,
  Instructions,
  N'aluminum OR polish'
) ct
INNER JOIN Production.ProductModel pm
  ON ct.[KEY]  =  pm.ProductModelID
ORDER BY ct.[RANK] DESC;
  SELECT
  ct.[RANK],
  ct.[KEY],
  pm.[Name] FROM CONTAINSTABLE
(
  Production.ProductModel,
  Instructions,
N'ISABOUT(aluminum WEIGHT(1.0 ), polish WEIGHT(0.1))'
) ct
INNER JOIN Production.ProductModel pm
  ON ct.[KEY]  =  pm.ProductModelID
ORDER BY ct.[RANK] DESC;

Statistical Semantics

When you created the index (see Figure 10-6) you had the option to select statistical semantics. Statistical semantics is new in SQL Server 2012 and it dramatically changes what it means to search documents. Everything discussed up to now was focused on searching words within a document. If you needed to find all the words similar to “weld,” you could find them by using FTS functions against text data stored in the SQL Server engine. But what if you wanted to find all the documents stored in your SQL Server database that were related to finance or a particular law case? Or, let’s say, you needed to search through hundreds of resumes to determine which ones best fit a particular job application. This is where statistical semantics becomes helpful. Statistical semantics is used to search for the meaning of documents and not just their content.

The statistical semantic feature requires FTS but is installed as a separate feature. The install file is located on the SQL Server install disk. The 64bit version is located at . . . x64Setup and the file name is SemanticLanguageDatabase.msi. The install wizard is straight-forward. The wizard extracts the semantic database files to a directory. The default directory is C:Program FilesMicrosoft Semantic Language Database. You will then want to copy or move these database files to another location, preferably the same location as your other database files, and then attach the database. Once the database is attached, run the command in Listing 10-24.

Listing 10-24. Initializing the Statistical Semantics Database

EXEC sp_fulltext_semantic_register_language_statistics_db @dbname  =  N'semanticsdb';

Once initialized, you can verify the database is ready by running the code in Listing 10-25. Figure 10-24 shows the results.

Listing 10-25. Verifying Active Statistical Semantics Database

SELECT * FROM sys.fulltext_semantic_language_statistics_database

From here you can now go back to the properties of the Production.ProductModel FTS index we created earlier in the chapter and checkmark the Statistical Semantics column as shown in Figure 10-25.

Now that statistical semantics is installed we can do things like search for key phrases or find related documents. To find a key phrase we use the TVF semantickeyphrasetable. Searching for key phrases on the Production.ProductModel name column yields the results we see in Figure 10-26. Run the code in Listing 10-26 to get the results.

Listing 10-26. Using the Semantickeyphrasetable Function

SELECT TOP(10) KEYP_TBL.keyphrase
FROM SEMANTICKEYPHRASETABLE
    (
    Production.ProductModel,
 Name
    ) AS KEYP_TBL
ORDER BY KEYP_TBL.score DESC;
GO

Semantic searching offers some interesting possibilities and broadens the scope of traditional FTS. If you include the SQL Server 2012 FileTable feature then the possibilities widen even further. FileTable allows documents stored on a file system to be integrated and managed through SQL Server. Semantic searching can be performed against these and any other document managed by the SQL Server engine.