2

I have followed the guidelines on how to index json fields from Microsoft here.

Here is an example:

ALTER TABLE Sales.SalesOrderHeader
ADD vCustomerName AS JSON_VALUE(Info,'$.Customer.Name')

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)

I have 2 computed columns though (should not matter I think), and then I get this warning:

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'IX_vType_vTime' has maximum length of 16000 bytes. For some combination of large values, the insert/update operation will fail.

This seems legitimate since JSON_VALUE returns nvarchar(4000), so maximum is 16000 bytes. Is there any need to do something with it?

E.g. conversion to nvarchar(50) during computation?

Ilya Chernomordik
  • 27,817
  • 27
  • 121
  • 207
  • @Larnu Not sure I understood you correctly? If you mean normalize the data in a classical SQL way I chose not to do that on purpose and use SQL a bit like a document database (that's the whole point with JSON data I assume) – Ilya Chernomordik Jan 19 '21 at 12:36
  • I misread what your goal is here, I thought you meant you want to index the JSON; which you can't. You can, however, index a column that uses the `JSON_VALUE` expression, but yes you'll need to convert the value to an appropriate data type; I doubt the value needs to be an `nvarchar(4000)` and should be a much shorter length or perhaps even a completely different data type. – Thom A Jan 19 '21 at 12:39
  • Seems the documentation needs a correction that you link to as well, as they don't explicitly `CONVERT`/`CAST` the value, so their [example](https://learn.microsoft.com/en-us/sql/relational-databases/json/index-json-data?view=sql-server-ver15#example-index) would generate the same error you have. – Thom A Jan 19 '21 at 12:45
  • Yes, It looks like the example can be updated. Ideally CAST should throw an exception if the field is too long as well, it will just get truncated otherwise, do you know if it's easily achieveable? – Ilya Chernomordik Jan 19 '21 at 13:02
  • Yes, it's very easy, just wrap your `JSON_VALUE` expression with `CONVERT` or `CAST`. – Thom A Jan 19 '21 at 13:08
  • FYI, it is by design that `CONVERT`/`CAST` truncate values. For example, `CONVERT(varchar(6),GETDATE(),112)` intentionally returns `'202101'`, not an error. This is [documented](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#truncating-and-rounding-results). – Thom A Jan 19 '21 at 13:14
  • thanks for you help! you can add an answer if you want that I can accept – Ilya Chernomordik Jan 19 '21 at 13:19

0 Answers0