Full-Text Search is now available in Azure SQL Database (GA)

With the growing popularity of storing and managing data using Azure SQL Database, demand for full-text search capabilities in a wide variety of applications is rapidly increasing. This article announces Full-Text Search is now available in Azure SQL Database V12, it gives a high level overview of the feature set available and current limitations.

What is Full-Text Search?

Full-Text Search allows fast and flexible indexing for keyword-based query of text data stored in tables with columns of following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) . Full-text indexes may be built not just on columns that contain text data, but also against tables with columns that contain files with built-in supported types (.html, .htm, .txt, .log, .xml, .java). Common uses of Full-Text Search include:

Custom applications that need to provide text search capabilities over data stored in a SQL Database.

Full-Text Search can scale from mobile or personal applications with relatively few and simple queries, up to complex mission-critical applications with high query volume over huge amounts of textual data.

Getting started

Full-Text Search is available in Premium, Standard and Basic service tiers in Azure SQL Database V12. You can start using it immediately on your databases as there is no other service configuration necessary. Full-Text Search provides integrated management capabilities:

Existing Microsoft SQL Server tooling can be used in conjunction with Full-Text Search (SSMS from latest SQL Server 2014 hotfix and May 2015 release of SQL Data Tools required).

Note: The hotfix is included in the latest release of SSMS SP1 CU1 onwards. You still need to install the May 2015 release of SQL Data Tools.

Here is a sample workflow that uses AdventureWorksLT database and highlights some of the search capabilities you have using Full-Text Search in Azure SQL Database:

Step 1: Create a full-text catalog

Example: Dave, the DBA, wants to create a default FT Catalog for his database.

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

Step 2: Create a full-text index

Example: Dave wants to create a FT Index on a table containing description of products. In this way, future queries over the products will be handled by FTS functionality. To do so, he must provide first a default FT Catalog and a unique index on the table to be full-text indexed.

Step 3: Start population

Example: Dave wants to enable the product details FT index. Right after he wants to start a full population of this FT index. This will start the process of parsing the data from the table and index it into the selected FT index.

Step 4: Query using full-text search predicates

Example: Dave, the Database Developer, is implementing the search side of a functionality that offers the ability to find all the products with a certain price level and product description containing the words "confortable" OR "ride"

You can also troubleshoot issues like indexing errors or unsupported document types using XEvents, now available in Azure Sql database. You can create and XEvent session using sqlserver.fulltextlog_written event as detailed here in order to access the FullText related errors.

Current limitations of Full-Text Search capabilities in Azure SQL Database

Azure SQL Database Full-Text Search or Azure Search?

Azure Search is a Microsoft Azure service that makes it easier for developers to build great search experiences into web and mobile applications. Using indexers for Azure SQL Database, users now have the option to search over their data stored in Azure SQL Database using Azure Search. Important to note is that Azure Search supports data from a variety of data sources, not just SQL Database. As customers can use either Full-Text Search or Azure Search for their data search requirements, one approach can prove more preferable than the other depending on scope of search, feature capabilities, location of data, scale or cost as highlighted below.

Conclusion

Search has become a natural way for users to interact with applications that manage large volumes of data. For applications that manage textual data stored in Azure SQL Database, Full-Text Search adds great value by providing fast, robust search functionality integrated into the database platform.