SQL Server 2017 Developer's Guide

Indexes on computed columns

The following code example creates a sample table with a JSON column and populates it with values from the Application.People table:

USE WideWorldImporters; 
DROP TABLE IF EXISTS dbo.T1; 
CREATE TABLE dbo.T1( 
id INT NOT NULL, 
info NVARCHAR(2000) NOT NULL, 
CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED(id) 
); 
 
INSERT INTO dbo.T1(id, info) 
SELECT PersonID, info FROM Application.People t1 
CROSS APPLY( 
  SELECT ( 
    SELECT t2.FullName, t2.EmailAddress, t2.PhoneNumber,      t2.FaxNumber    FROM Application.People t2 WHERE t2.PersonID = t1.PersonID FOR      JSON AUTO, WITHOUT_ARRAY_WRAPPER 
  ) info 
 ) x  

Assume you want to return rows that have the Vilma Niva value for the FullName property. Since this is a scalar value, you can use the JSON_VALUE ...