Sql-server – Why is this query on JSON values in SQL Server using an index scan instead of an index seek

indexjsonsql serversql-server-2017

I have a table with the following schema:

CREATE TABLE [dbo].[Obj1Json](
    [ObjectID] [int] IDENTITY(1,1) NOT NULL,
    [PointerToSource] [nvarchar](255) NULL,
    [CreateDate] [datetime2](7) NOT NULL,
    [ModifiedDate] [datetime2](7) NOT NULL,
    [Indexes] [nvarchar](max) NULL,
    [vAccountID]  AS (json_value([Indexes],'$.AccountID'))

where the Indexes column contains JSON that looks like:

{
  "AccountID": 73786,
  "AccountName": "5869b4e9-f441-463f-8f6d-93b4f4ff8c75",
  "ProcessLocation": "Start",
  "IsPasswordProtected": true,
  "InvoiceDate": "2020-12-30T09:00:32.8473077-05:00"
}

The vAccountID column is used for an index on the JSON in Indexes:

CREATE INDEX IDX_Obj1Json_AccountID
ON Obj1Json(vAccountID)

The table has about 10.5 million rows of randomly generated data (all data in the Indexes column has the same structure, however). The query I'm running is

SELECT JSON_VALUE(Indexes, '$.AccountID')
  FROM [Obj1Json]
  WHERE JSON_VALUE(Indexes, '$.AccountID') = 69725

which returns 110 results. When looking at the execution plan in SSMS, I see that an index scan is being used, whereas I would expect an index seek to be used instead.

Query plan of the above query, showing an index scan being used

The query returns quickly (under a second), but I'm curious why an index seek isn't being used. Is there any obvious reason why SQL Server is using an index scan instead of a seek?

Best Answer

The issue is that JSON_VALUE returns an NVARCHAR and you sent in an INTEGER. This causes a Convert_Implicit.

If you pass in a VARCHAR or NVARCHAR param/value into the WHERE condition, it does a seek and drops the rows read.

Related Question