November 30, 2018 at 2:59 am
Hello, All!
Trying to convert a JSON-output to a variable I get the warning about the conversion. Is it so bad as it seems? And is there a way to avoid it?
First query, no variable - no problem
Create table #tbl (Name VarChar (128))
Insert Into #tbl
Values
('A'), ('B'), ('C')
Select *
From
#tbl
For
JSON PATH
Drop table #tbl
Second query: implicit conversion
Declare @res VarChar (Max)
Create table #tbl (Name VarChar (128))
Select @res =
(
Select *
From
#tbl
For
JSON PATH,
WITHOUT_ARRAY_WRAPPER
)
drop table #tbl
Try to convert - still get the warning
Declare @res VarChar (max)
Create table #tbl (Name VarChar (128))
Select @res = Convert (VarChar (max),
(
Select *
From
#tbl
For
JSON PATH,
WITHOUT_ARRAY_WRAPPER
))
drop table #tbl
I've added XML plans to the text files.
November 30, 2018 at 8:55 am
Peter
I've just tried but don't get back any warning
Just launching
Declare @res1 VarChar (Max)
Declare @res2 VarChar (Max)
Create table #tbl (Name VarChar (128))
Insert Into #tbl Values ('A'), ('B'), ('C')
Select @res1 = ( Select * From #tbl For JSON PATH, WITHOUT_ARRAY_WRAPPER )
Select @res2 = Convert (VarChar (max), ( Select * From #tbl For JSON PATH, WITHOUT_ARRAY_WRAPPER))
select ( Select * From #tbl for JSON PATH)
union all select @res1
union all select @res2
drop table #tblNovember 30, 2018 at 9:03 am
You may see the warning in the execution plan
November 30, 2018 at 9:33 am
Peter Shilovich - Friday, November 30, 2018 9:03 AMYou may see the warning in the execution plan
The reason you are getting that warning is the JSON strings are NVARCHAR(MAX). You will lose the warnings if you change your VARCHAR(MAX)'s to NVARCHAR(MAX)'s.
November 30, 2018 at 9:53 am
@Lynn
correct, but when launching this query the warn moves from the select to the insert
I really don't get the reason why... maybe literals are interpreted as NVARCHAR(1) ?
that's odd
create table #tbl (name nvarchar(max))
insert #tbl (name) values (N'A'), (N'B')
declare @res nvarchar(max) = (select name From #tbl for json path)
drop table #tbl November 30, 2018 at 10:15 am
carlo.pagliei 63874 - Friday, November 30, 2018 9:53 AM@Lynn
correct, but when launching this query the warn moves from the select to the insert
I really don't get the reason why... maybe literals are interpreted as NVARCHAR(1) ?
that's oddcreate table #tbl (name nvarchar(max))
insert #tbl (name) values (N'A'), (N'B')
declare @res nvarchar(max) = (select name From #tbl for json path)
drop table #tbl
And if you change the data type for the column name from nvarchar(max) to nvarchar(128) the implicit conversion to nvarchar(max) goes away.
November 30, 2018 at 10:36 am
Lynn Pettis - Friday, November 30, 2018 9:33 AMPeter Shilovich - Friday, November 30, 2018 9:03 AMYou may see the warning in the execution planThe reason you are getting that warning is the JSON strings are NVARCHAR(MAX). You will lose the warnings if you change your VARCHAR(MAX)'s to NVARCHAR(MAX)'s.
Thanks 🙂
November 30, 2018 at 10:45 am
That warning is only a problem when you're filtering on the values. If you're simply retrieving the columns (JSON or not), it shouldn't negatively affect you. I'd still follow Lynn's advice though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply