This post was last updated on October 12, 2015.
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:
- Web-based applications (searching websites, product catalogs, news items, and other data),
- Document management systems,
- 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.
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.
- Azure SQL Database Full-Text Search syntax is 100% compatible with Microsoft SQL Server Transact-SQL query syntax.
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.
CREATE UNIQUE INDEX ui_ukProductDescription ON SalesLT.ProductDescription(ProductDescriptionID); CREATE FULLTEXT INDEX ON SalesLT.ProductDescription(Description) KEY INDEX ui_ukProductDescription ON ftCatalog;
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.
ALTER FULLTEXT INDEX ON SalesLT.ProductDescription ENABLE; GO ALTER FULLTEXT INDEX ON SalesLT.ProductDescription START FULL POPULATION;
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"
SELECT Name, ListPrice, Description FROM SalesLT.ProductDescription pd JOIN SalesLT.ProductModelProductDescription pmpd ON pd.ProductDescriptionID=pmpd.ProductDescriptionID JOIN SalesLT.Product p ON p.ProductModelID=pmpd.ProductModelID WHERE ListPrice <100 AND CONTAINS(Description, 'confortable OR ride');
Example: Dave is now implementing the search side of a functionality that allows finding any product that has forms of “ride” in the description.
SELECT Name, ListPrice, Description FROM SalesLT.ProductDescription pd JOIN SalesLT.ProductModelProductDescription pmpd ON pd.ProductDescriptionID=pmpd.ProductDescriptionID JOIN SalesLT.Product p ON p.ProductModelID=pmpd.ProductModelID WHERE CONTAINS(description, 'FORMSOF(FREETEXT, ride)');
Step 5: Monitor full-text search activity
Example: Dave wants to view the information about in-progress index population.
SELECT * FROM sys.dm_fts_index_population
Example: Dave wants to view the content of the full-text index.
SELECT * FROM sys.dm_fts_index_keywords( DB_ID('AdventureWorksLT'), OBJECT_ID('SalesLT.ProductDescription'))
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
Note that Azure SQL Database V12 Full-Text Search functionality represents a subset of the Microsoft SQL Server 2014 release, here is the short list of gaps:
- No support for installation or use of third party filters, including Office and .pdf.
- Customers cannot manage service settings for fdhost, all configurations are being managed by the service.
- Semantic search, thesaurus and search property lists syntax is not yet enabled.
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.
Azure SQL Database Full-Text Search approach
Azure Search approach
|Location of Data||Run queries against character-based data stored within SQL Database.||Search data from any data source, not just Azure SQL Database.|
|Migration||Lift & shift on premise Full-Text Search enabled databases to Azure SQL Database.||N/A|
|Scale & Performance||Keep Full-Text Search data results and transactional data together. For example, join Full-Text Search results with other tables and views.||Isolate search workload from OLTP workload for performance and scale considerations.|
|Feature differentiation||Feature integrated in Azure SQL Database. Search capabilities are not used frequent enough to justify cost for additional service.||Fully-managed search service for web and mobile applications. Rich search experience with custom scoring models, auto complete for suggestions, faceted search, hit highlighting.|
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.