改进SQL Server查询将任意表转换为JSON

成为

经过大量的搜索和拼凑，使用了这些技术Web上的FOR
XML和.nodes()命令转换结果集，创建合理的查询(而不是存储过程)SQL查询转换为JSON优秀的数组工作。
查询将每个数据行编码为带有前导逗号的单个数据JSON对象。数据行用方括号，然后将整个结果集导出文件。
我想看看外面是否有人能看到改善其性能的方法？
这是带示例表的查询:
declare @xd table (col1 varchar(max),col2 int,col3 real,colNull int)insert into @xd select '',null,null,nullUNION ALL select 'ItemA',123,123.123,nullUNION ALL select 'ItemB',456,456.456,nullUNION ALL select '7890',789,789.789,nullselect{}UNION ALLselect ，{ STUFF( (select    "' r.value('local-name(.)','varchar(max) ":'       case when r.value('./@xsi:nil','varchar(max)') = 'true' then 'null       when isnumeric(r.value('.','varchar(max)')) = 1          then r.value('.','varchar(max)       else '"' r.value('.','varchar(max) "       end from rows.nodes('/row/*') as x(r) for xml path('')) ),1,1,' }'from  -- Arbitrary query goes here,(fields go where t.* is,table where @xd t is) select (select t.* for xml raw,type,elements XSINIL) rows from @xd t) xdUNION ALLselect ']'我对此最大的批评是它运行缓慢。
目前完成4.2万行左右需要3:30。
我的另一个批评是，它假设所有看起来像数字的东西都是数字。它不会试图找到最少的列类型（我不确定是否可以）。
最后的次要批评是，第一个数据银行将在前面添加逗号。从技术上讲，它不应该这样做。为了弥补这一点，它需要启动JSON在数组的第一行中使用空的JSON对象。
当邀请其他评论（最好使用解决方案）时，我唯一真正的限制是解决方案可以在许多任何方面进行SQL重复查询，无需显式标识列名。
我正在使用SQL Server 2012。
感谢其他像我这样的人，他们正在寻找广义SQL结果-> JSON请享受数组转换器！

解决方案:
                                                            我说，如果您真的想提高性能，请使用元编程。下面的示例使用40,000行进行尝试，并在不到一秒的时间内返回结果（不算插入初始的40k好吧，在这个例子中只需要2秒左右)。它还考虑到你的数据类型，不要用引号引起数字。
declare @xd table (col1 varchar(max),col2 int,col3 real,colDate datetime,colNull int);declare @i int = 0;while @i < 10000 begin set @i  = 1； insert into @xd select '',null,null,null,null union all select 'ItemA',123,123.123,getDate(),null union all select 'ItemB',456,456.456,getDate(),null union all select '7890',789,789.789,getDate(),null;end;select *into #json_basefrom  -- Insert SQL Statement here select * from @xd) t;declare @columns table  id int identity primary key,          name sysname, datatype sysname, is_number bit, is_date bit);insert into @columns(name,datatype,is_number,is_date)select columns.name,types.name,    case when number_types.name is not NULL          then 1 else 0       end as is_number,    case when date_types.name is not NULL          then 1 else 0       end as is_datefrom tempdb.sys.columnsjoin tempdb.sys.types on (columns.system_type_id = types.system_type_id)left join (values ('int'),('real'),('numeric       decimal'),('bigint'),('tinyint')) as number_types(name) on (types.name = number_types.name)left join (values ('date'),('datetime'),('datetime2       smalldatetime'),('time'),('datetimeoffset')) as date_types(name) on (types.name = date_types.name)where object_id = OBJECT_ID('tempdb..#json_base');declare @field_list varchar(max) = STUFF(( select QUOTENAME(QUOTENAME(name,'" ''''''''case when is_number =                      then 'COALESCE(LTRIM             QUOTENAME(name)  nullwhen is_date =                      then 'COALESCE(QUOTENAME(LTRIM(convert(varchar(max),                                     QUOTENAME(name)  "nullelse 'COALESCE(QUOTENAME             QUOTENAME(name) ',''"null          end from @columns for xml path  1，5，')；create table #json_result  id int identity primary key, line varchar(max));declare @sql varchar(max) = REPLACE( insert into #json_result  select{''''{f} ''}''  from #json_base','{f}',@field_list);exec(@sql);update #json_resultset line = STUFF(line,1,1,'')where id = 1;select '['UNION ALLselect linefrom #json_resultUNION ALLselect ']';drop table #json_base;drop table #json_result;

改进SQL Server查询将任意表转换为JSON

成为 LV1