0

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?

2
  • It is having to implicitly convert the values stored in the index to integers in order to compare them to your input. Perhaps use '69725' instead Jan 5, 2021 at 19:25
  • Just curious why aren't you using your vAccountID column in the WHERE clause of your query anyway?
    – J.D.
    Jan 5, 2021 at 19:32

1 Answer 1

4

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.

3
  • That did the trick, good catch! Thanks!
    – DylanSp
    Jan 5, 2021 at 19:52
  • 2
    If it's guaranteed to always be an integer, it might be worth it to redefine the column to match, e.g. vAccountID AS CONVERT(int, (JSON_VALUE(Indexes, '$.AccountID'))) Jan 5, 2021 at 21:49
  • @ErikDarling Also good to know, I didn't think of that.
    – DylanSp
    Jan 6, 2021 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.