Mastering SQL Server 2017

Full-text indexes

One of the advantages of the fact that JSON data is stored as text in SQL Server is that you can use full-text search features. With computed columns, as demonstrated in the previous section, you can index only one property. To index all JSON properties (actually, to simulate this) you can use full-text indexes.

To demonstrate how full-text searching can improve JSON query performance, you first create a full-text catalog and index it in the sample table that you created earlier in this section:

USE WideWorldImporters; 
CREATE FULLTEXT CATALOG ftc AS DEFAULT;   
CREATE FULLTEXT INDEX ON dbo.T1(info) KEY INDEX PK_T1 ON ftc; 

Now, after you have created a full-text index, you can execute JSON queries to check whether they can ...