SQL Server supports mechanisms that allow you to store large amounts of unstructured text information. In older versions of SQL Server, you had the TEXT data type. With the more modern versions of SQL Server, you can use MAX values with the variable-length character data types VARCHAR and NVARCHAR. This means you can store up to 2GB worth of textual information within a single column. Since a standard index can hold only up to 900 bytes of information, there has to be another mechanism for searching within these large data types. That’s where full-text indexing comes into play.

Full-Text Indexing

Full-text search indexing is another indexing feature in SQL Server, outside the normal indexing methods and objects. This chapter will briefly describe the full-text search architecture, storage, and indexing for optimal performance.

Full-text search (FTS) allows you to store large amounts of text-based content. This content can include a number of document types including formats such as Word document (.doc) files. This storage is then in BLOB columns instead of plain-text data. The ability to search and store content of an unstructured nature provides a number of opportunities in a database management system.

Document retention is one such opportunity; it allows you to store documents for vast lengths of time at a much lower cost. The search abilities allow for querying this content for all types of needs. Imagine a shipping company that creates thousands of shipping documents from a template created in plain text. Those documents create a massive initiative for retention purposes to ensure shipments can be tracked for later needs. Storage warehouse rooms cost money to maintain. When the task of researching a specific shipment arises, the hours taken for that task are significant.

Now imagine this shipping company is using the FTS feature and an indexing structure. The documents are scanned with systems that read the text into a system that later inserts this data into a SQL Server database. This allows for a full-text search of specific account numbers, shipping invoices, and any distinct text in the documents needed for later review. An index just like a book index can be created, making it even quicker to find specific documents. Going further, FTS lets you search for specific content in the documents themselves. If a request comes in to find all shipping documents that were sent by a specific freight company on a specific trailer, the FTS capabilities allow the information to be retrieved in a fraction of the time as compared to a manual process.

Creating a Full-Text Example

Now that you understand the concept of FTS, let’s look at the indexing strategy. Full-text indexes are essentially the backbone of searching and querying the data. This data can be a number of data types including char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, and varbinary(max). Since some of these data types are on the deprecation list, it’s probably best to avoid their use. These include text, ntext, and image. They’re all easily replaced by the other data types. In fact, utilizing varchar(max) on most 64-bit systems has outperformed other data types in the latest tests by the CAT team, as described in “Best Practices for Integrated Full Text Search (iFTS) in SQL 2008” (http://bit.ly/1L9fBn2). This is, in part, because of data types such as char using a direct parsing mechanism and the others using specialized processing. 64-bit systems and FTS tend to outperform 32-bit systems because of the memory-intensive use of the FTS process. When 32-bit systems are in use, the memory consumption should be configured and monitored carefully. This is partly why FTS on larger installations is utilized on designated hardware and databases. This allows the database administrator to maintain a system for FTS specifically.

For the remainder of this section on full-text search indexing, the contents of the white paper “Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator” by Joseph Sack have been inserted into the sample table using the script in Listing 6-1. I’ll use the document to demonstrate full-text indexing. You can find the document in Books Online or download it from http://bit.ly/1II5UfU. You need to make sure that you have the Full Text Catalog feature installed in order to successfully run all the commands in this chapter. Full-text is not a feature that is installed by default, so you may need to address that first.

Using AdventureWorks, a table can be prepared that will be used for full-text searching. Using the varbinary(max) data type allows the import of most document types and images. In Listing 6-1, the CREATE TABLE and INSERT statements prepare the objects needed to create a full-text search index.

CREATE TABLE SQLServerDocuments (
     SQLServerDocumentsID INT IDENTITY(1, 1),
     DocType VARCHAR(6),
     DOC VARBINARY(MAX),
     CONSTRAINT PK_SQLServerDocuments PRIMARY KEY CLUSTERED
        (SQLServerDocumentsID)
    );
GO
DECLARE @worddoc VARBINARY(MAX);
SELECT  @worddoc = CAST(bulkcolumn AS VARBINARY(MAX))
FROM    OPENROWSET(BULK 'Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.docx',
                   SINGLE_BLOB) AS x;

INSERT  INTO dbo.SQLServerDocuments
        (DocType, DOC)
VALUES  ('docx', @worddoc);
GO

Creating a Full-Text Catalog

When creating an FTS index, a full-text catalog must first be created. Prior to SQL Server 2008, this catalog was a physical object and was optimized by designating a specific file group for the catalog. In versions of SQL Server after 2008, the catalog is now contained in the database as a definition. The catalog itself is now a virtual object and greatly enhances the performance by eliminating I/O bottlenecks. A catalog contains all the properties that are searchable.

The catalog is the link to the full-text index. To create a new full-text catalog, use the CREATE FULLTEXT CATALOG syntax shown in Listing 6-2.

CREATE FULLTEXT CATALOG <catalog name>
WITH <catalog specific options>
AS DEFAULT
AUTHORIZATION <the owners name – ownership>
ACCENT_SENSITIVITY = <ON|OFF>;

The first option that should be considered in the creation of a catalog is the AS DEFAULT setting. Commonly, full-text indexes are created without thought of the catalog to which they should be applied. If the catalog is omitted in the index creation, the catalog that has been set as the default will be used.

Authorization and accent sensitivity are specific in the CREATE command. When omitting the authorization option, ownership will fall under dbo. This is the same for most objects in SQL Server when ownership is not declared. It is recommended that you assign ownership for managing security and grouping objects under the proper areas. When specifying a user for ownership, you must specify a user name matching one of the following:

• The name of the user running the statement
• The name of a user that the user executing the command has impersonate permissions for
• The database owner or system administrator

Accent sensitivity dictates whether the catalog will be accent sensitive or insensitive. Be sure to research whether accent sensitivity should be on or off prior to the creation of the catalog. If this option is changed, the full-text indexes on the catalog must be rebuilt.

Execute the statement in Listing 6-3 to create a catalog as the default to be used with the white paper inserted into the table.

CREATE FULLTEXT CATALOG WhitePaperCatalog AS DEFAULT;

Creating a Full-Text Index

With the catalog created and the decision made for how you want to handle catalogs, accent sensitivity, and ownership, now you need to make some decisions and apply some restrictions to the creation of the full-text index. The most critical of these decisions is the requirement of a key index.

Syntax

Create the full-text index using the syntax in Listing 6-4. Table 6-1 describes the different options available.

CREATE FULLTEXT INDEX ON <table name>
(<column name>)
KEY INDEX <index name [must be specified]>
ON <catalog filegroup>
WITH <index options>
CHANGE_TRACKING = <Manual | Auto | Off>
STOPLIST = <default system or specified stoplist name>;

In most other CREATE INDEX statements, the basic syntax and options are alike with slight modifications. With FTS index creation, you can see there is a completely different set of options and considerations. The initial CREATE FULLTEXT INDEX is the same as any CREATE INDEX, with the given table required and then column to index. After this, the other options are not typical to normal index creations.

Key Indexes

Choosing the key index can be a straightforward choice given the restrictions of the key index being a unique, single-key, and non-nullable column. A primary key will commonly work well for this, like the primary key shown in Listing 6-1 on the SQLServerDocuments table. However, thought should be given to the size of the key. Ideally, a 6-byte key is recommended and documented as optimal to reduce overhead on I/O and CPU resource consumption. Recall that one of the restrictions of the unique key is that it cannot exceed 900 bytes. If this maximum restriction is met, the population will fail. Resolving the problem could force a new index and alteration of the table itself to occur. This could create costly downtime for tables that are in high-use situations.

Population

Change tracking in full-text indexing should be weighed heavily when creating full-text indexes. The default setting of AUTO may have overhead that can affect the performance negatively if the contents of the column being indexed change frequently. For example, a system that is storing shipping invoices that never change and are inserted only once a month would not likely benefit from AUTO being set. A MANUAL population would most likely be better run at a given time by using the SQL Server Agent based on the loading of the contents in the table. Although not common, some systems are static and loaded only one time. This would be an ideal situation for using the OFF setting, with the initial population being performed only at that time.

The last option for population is incremental population. It is an alternative to manual population. Incremental population is the same concept as an incremental update to data. As you run through the data and changes are made, they are tracked. Think of merge replication as a comparison. Merge replication retains changes by the use of triggers and insert/update/delete tracking rows into merge system tables. At a given point in time, a DBA can set a synchronization schedule to process those changes and replicate them to the subscribers. This is the same way incremental population functions. By using a timestamp column in the table, the changes are tracked. Only those that are found needing a change are processed. This does mean the requirement for a timestamp column on the table must be met in order to perform incremental populations. For data that has an extreme amount of change, this may not be ideal. However, for data that changes randomly and seldomly, incremental population may be suited for the installation.

StopLists

StopLists are extremely useful in managing what not to populate. This can improve the population performance by bypassing what are known as noise words. As an example, consider the sentence “A dog chewed through the fiber going to the SAN causing the disaster and recovery plans to be used for the SQL Server instance.” In this sentence, you would most likely want fiber, SAN, disaster, recovery, and SQL, or Server indexed. The A, the, to and be words would not be ideal. These are considered noise words and are not part of the population process. As you can imagine, the use of StopList can be extremely helpful in the overall population performance and parsing of the content. Use of the StopList can be specific to languages as well. For example, la in French would be specified over the in English.

To create a custom StopList, use the CREATE FULLTEXT STOPLIST statement as shown in Listing 6-5. The system default StopList can be used to pregenerate all the noise words already identified as such. For the white paper example, the name of the StopList would be WhitePaperStopList.

CREATE FULLTEXT STOPLIST WhitePaperStopList FROM SYSTEM STOPLIST;

To view the StopList, use the system views sys.fulltext_stoplists and sys.fulltext_stopwords. The sys.fulltext_stoplists view will hold metadata related to the stoplists that are created on the SQL Server instance. Determine the stoplist_id to join to sys.fulltext_stopwords to show a complete listing of the words. Alone, this StopList is no better than the system default StopList. To add words to the StopList, use the ALTER FULLTEXT STOPLIST statement in Listing 6-6’s example. That example removes Downtime as a word to be excluded.

ALTER FULLTEXT STOPLIST WhitePaperStopList ADD 'Downtime' LANGUAGE 1033;

To review the StopList words, run the query shown in Listing 6-7.

SELECT  lists.stoplist_id,
        lists.name,
        words.stopword
FROM    sys.fulltext_stoplists AS lists
JOIN    sys.fulltext_stopwords AS words
        ON lists.stoplist_id = words.stoplist_id
WHERE   words.language = 'English'
ORDER BY lists.name;

You can see the query results in Figure 6-1; the word Downtime has been successfully added.

With the catalog, StopList, and key index availability within the primary key SQLServerDocumentsID in the table created in Listing 6-1, you can create a full-text index on the DOC column in the same table from Listing 6-1. To create a full-text index, use the CREATE FULLTEXT INDEX statement (see Listing 6-8).

CREATE FULLTEXT INDEX ON dbo.SQLServerDocuments
(
DOC
TYPE COLUMN DocType
)
KEY INDEX PK_SQLServerDocuments
ON WhitePaperCatalog
WITH STOPLIST = WhitePaperStopList;

Once the index is created, population will begin since there was no option added for CHANGE_TRACKING. Later in the chapter I show how to monitor the catalog and see the status. It might take a while to load depending on the size of your document. The default AUTO setting takes effect. To query the content of the SQLServerDocuments table and Doc column, you can run a CONTAINS statement to return a specific word. Listing 6-9 shows an example of such a statement.

SELECT  ssd.DOC,
        ssd.DocType
FROM    dbo.SQLServerDocuments AS ssd
WHERE   CONTAINS (ssd.DOC, 'statistic');

Figure 6-2 shows the execution plan from the query.

By searching by means of CONTAINS(Contents,'replication'), the execution plan in Figure 6-2 shows the operation on FulltextMatch. It also returns the white paper with a document type of .docx as a match for this word search.

Full-Text Search Index Catalog Views and Properties

SQL Server provides a wealth of information about indexes in general. Performance, configurations, usage, and storage are just a few. As with normal index objects, full-text indexes require the same attention and detail to maintenance and options set to ensure they consistently benefit the overall performance rather than hinder it.

Table 6-2 describes the catalog views available to full-text search.

For informational purposes, while reviewing catalogs, properties, and status results for population, invoke the FULLTEXTCATALOGPROPERTY function, as shown in Listing 6-10.

FULLTEXTCATALOGPROPERTY ('catalog_name' ,'property')

The returned information will provide a wealth of detail on the state of the catalog including population status. The catalog_name parameter will take any catalog created, and then a listing of properties can be utilized to return the specific information required. Table 6-3 lists the properties you can pass.

For example, to show the population status of the WhitePaperCatalog catalog used earlier, you can use the statement in Listing 6-11.

SELECT FULLTEXTCATALOGPROPERTY('WhitePaperCatalog','PopulateStatus');

Catalogs and the referencing indexes can be reviewed by executing sys.fulltext_index_catalog_usages. This catalog view returns all the indexes that have been referenced from it, as shown in Listing 6-12.

SELECT  OBJECT_NAME(ficu.object_id) [Object Name],
        ficu.index_id,
        ficu.fulltext_catalog_id
FROM    sys.fulltext_index_catalog_usages AS ficu;

For detailed information on all catalogs and settings currently applied to them, query sys.fulltext_catalogs. This catalog view is helpful in determining the default catalog and property status indicators, such as is_importing that shows whether the catalog is in the process of being imported.

For a detailed review of the full-text indexes in the database, you can use sys.fulltext_indexes along with joining catalog views to create a more meaningful resultset. Important information from this catalog view consists of the full-text catalog name and properties; change tracking property, crawl type, and state; and the StopList set to be used.

The query in Listing 6-13 returns an information resultset of all indexes including catalog and StopList information for the index.

SELECT  idx.is_enabled,
        idx.change_tracking_state,
        idx.crawl_type_desc,
        idx.crawl_end_date [Last Crawl],
        cat.name,
        CASE WHEN cat.is_accent_sensitivity_on = 0 THEN 'Accent InSensitive'
             WHEN cat.is_accent_sensitivity_on = 1 THEN 'Accent Sensitive'
        END [Accent Sensitivity],
        lists.name,
        lists.modify_date [Last Modified Date of StopList]
FROM    sys.fulltext_indexes idx
JOIN    sys.fulltext_catalogs cat
        ON idx.fulltext_catalog_id = cat.fulltext_catalog_id
JOIN    sys.fulltext_stoplists lists
        ON idx.stoplist_id = lists.stoplist_id;

Summary

This chapter outlined how to create and query from a full-text index. The need to be able to filter and query on large documents and free-form text is just as important as being able to use standard structured indexes. With full-text indexing, you can use examine not only the contents of a column but also the contents of a file within a column, allowing applications to much better identify documents and other artifacts that match contextually with the requests being submitted.