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 use full-text index benefits. You need to use the CONTAINS predicate; it can identify rows where a word is near another word. Here is the query:

SELECT id, info 
FROM dbo.T1 
WHERE CONTAINS(info,'NEAR(FullName,"Vilma")'); 

The execution plan for the query shown in the following screenshot clearly demonstrates that a full-text index was helpful for this query:

To ensure that the same index can improve performance for JSON queries searching the other JSON properties and not only FullName (as in the case of the index on the computed column), let's execute another query that searches the PhoneNumber property:

SELECT id, info 
FROM dbo.T1 
WHERE CONTAINS(info,'NEAR(PhoneNumber,"(209) 555-0103")'); 

The execution plan is the same as for the previous query, as you can see in the following screenshot: 

The same index covers both queries. Unfortunately, JSON path expressions are not supported in the CONTAINS predicate; you can only search for property values, but it is better than scanning the whole table.

You can store and process small and moderate amounts of JSON data within SQL Server with good support of JSON functions and acceptable performance. However, if your JSON documents are large and you need to search them extensively, you should use a NoSQL solution, such as DocumentDB.

Ensure that you have dropped the table used in this exercise:

USE WideWorldImporters; 
DROP TABLE IF EXISTS dbo.T1;