SQL Server: Synthesize ordering column for parameter query roots with OPENJSON

[roji]

Currently, when querying a parameter collection on SQL Server, where the ordering needs to be preserved (i.e. limit/offset), we generate OPENJSON without WITH, so that we can order by the projected key column:

@__Skip_0='["a", "b","c"]' (Size = 4000)

-- ...
SELECT CAST([s].[value] AS int)
FROM OPENJSON(@p) AS [s]
ORDER BY [s].[key]

Instead, we could change the parameter collection, synthesizing an ordering column to be projected out with WITH:

@__Skip_0='[{ "ord": 1, "v": "a" }, { "ord": 2, "v": "b" }, { "ord": 3, "v", c" }]' (Size = 4000)

-- ...
SELECT CAST([s].[value] AS int)
FROM OPENJSON(@p) WITH ([_ord] int '$.ord', [value] int '$.v') AS [s]
ORDER BY [s].[_ord]

Note that this optimizes parameter collections only; column collections will obviously need to continue using OPENJSON without WITH since we can't synthesize ordering values there. Note also that where ordering isn't required, we already do the efficient thing (OPENWITH with WITH, no ordering); so this optimization would only help cases where ordering is required.

Credit to @yv989c for implementing this technique in QueryableValues, see #13617 (comment)