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

改进SQL Server查询以将任意表转换为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 = 1  then 'COALESCE(LTRIm('          + QUOTENAME(name) + '),''null'')'  when is_date = 1  then 'COALESCE(QUOTENAME(LTRIm(convert(varchar(max), '          + QUOTENAME(name) + ', 126)),''"''),''null'')'  else '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;