Improve SQL Server query to convert arbitrary table to JSON

I say if you really want to kick up performance, use metaprogramming. The example below tries this with 40,000 rows and returns results in less than a second (not counting inserting the initial 40k rows, which in this example only takes about 2 seconds). It also takes into account your data types to not enclose numbers in quotes.

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;

From Firoz Ansari:

CREATE PROCEDURE [dbo].[GetJSON] (@ParameterSQL AS VARCHAR(MAX))ASBEGINDECLARE @SQL NVARCHAR(MAX)DECLARE @XMLString VARCHAR(MAX)DECLARE @XML XMLDECLARE @Paramlist NVARCHAR(1000)SET @Paramlist = N'@XML XML OUTPUT'SET @SQL = 'WITH PrepareTable (XMLString) 'SET @SQL = @SQL + 'AS ( 'SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW, TYPE, ELEMENTS 'SET @SQL = @SQL + ') 'SET @SQL = @SQL + 'SELECT @XML = XMLString FROM PrepareTable 'EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUTSET @XMLString = CAST(@XML AS VARCHAR(MAX))DECLARE @JSON VARCHAR(MAX)DECLARE @Row VARCHAR(MAX)DECLARE @RowStart INTDECLARE @RowEnd INTDECLARE @FieldStart INTDECLARE @FieldEnd INTDECLARE @Key VARCHAR(MAX)DECLARE @Value VARCHAR(MAX)DECLARE @StartRoot VARCHAR(100); SET @StartRoot = ''DECLARE @EndRoot VARCHAR(100); SET @EndRoot = ''DECLARE @StartField VARCHAR(100); SET @StartField = ''SET @RowStart = CharIndex(@StartRoot, @XMLString, 0)SET @JSON = ''WHILE @RowStart > 0BEGIN    SET @RowStart = @RowStart+Len(@StartRoot)    SET @RowEnd = CharIndex(@EndRoot, @XMLString, @RowStart)    SET @Row = SubString(@XMLString, @RowStart, @RowEnd-@RowStart)    SET @JSON = @JSON+'{'    -- for each row    SET @FieldStart = CharIndex(@StartField, @Row, 0)    WHILE @FieldStart > 0    BEGIN        -- parse node key        SET @FieldStart = @FieldStart+Len(@StartField)        SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)        SET @Key = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)        SET @JSON = @JSON+'"'+@Key+'":'        -- parse node value        SET @FieldStart = @FieldEnd+1        SET @FieldEnd = CharIndex('0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))    SET @JSON = @JSON+'},'    --/ for each row    SET @RowStart = CharIndex(@StartRoot, @XMLString, @RowEnd)ENDIF LEN(@JSON) > 0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))SET @JSON = '[' + @JSON + ']'SELECT @JSONEND