FOR XML PATH and OPTION() can't be together in query?
7
votes
2
answers
455
views
I have a weird edge case where trying to combine FOR XML PATH('') with OPTION(FORCE ORDER) is not parsing as valid sql for some reason, and I can't find any explanation so far with Google. The parse error just says "Incorrect syntax near the keyword 'OPTION'." but I'm not clear why. Anyone have an explanation?
I was asked to improve the performance of an ETL process that packs a bunch of transaction details into a string to insert into a json blob from a Sql Server 2019 database. This is a detail table with about 2.6 billion rows that get packed into about 325 million final results.
I tried a number of things with incremental improvements (the original implementation was using the
DECLARE @result nvarchar(max) = ''
SELECT @result = @result + ...
concatenation method, which turned out to be the least efficient of all I could think of) but then I looked at table compression.
PAGE compression turned out to be very profitable. Not only did the details table go from 148 gig to 30 gig, in some query plans the order of the joins also made it take 1/3 the time. When this happened, the work table joined on ID to the details table first and then to the type definition table.
So I set the table definition for the next build to use PAGE compression and, as might be expected, didn't get quite as good compression loading into an empty table. Instead of 30 gig, it was 45 gig - still a big space savings, but unless I got maximal compression, the plan produced wasn't optimal.
If I rebuild the PAGE compressed primary key and get it back to max compression, the plan comes out fast, but anything other produces a plan that takes a lot longer. So I tried to add OPTION (FORCE ORDER) to the query to see if I could get it down the path I wanted.
CREATE FUNCTION [dbo].[fnGetDetailsJSON](@ID int)
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ss nvarchar(max) = (SELECT N','+(N'"Type' + RTRIM(ST.TypeCode))+ N'":"' + D.DisplayValue + N'",' + (N'"Type' + RTRIM(ST.TypeCode)) + N'IPCV":"' + CONVERT(varchar(20),d.IPCV) + N'"'
FROM [Transactions].[dbo].[DetailsMaster] (NOLOCK) D
INNER JOIN [Transactions].dbo.DetailTypes DT WITH(NOLOCK,INDEX([ucoixTransactionTypes-TypeID-Display])) ON D.TypeID = DT.SignalTypeID AND DT.DisplayInProduct = 1
WHERE D.ID = @ID
order by D.TypeID
FOR XML PATH('')
-- OPTION (FORCE ORDER) won't let FOR XMLPATH() and OPTION(FORCE ORDER) exist on the same query
)
RETURN @SS
END
GO
used in a query like this:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
DECLARE @MinRowNumber int = 0, @MaxRowNumber int = 30000, @StartTime Date= GETDATE()
SELECT ID, dbo.fnGetDetailsJSON(ID), @StartTime
FROM dbo.WorkList n
where n.RowNumber > @MinRowNumber and n.RowNumber <= @MaxRowNumber
Asked by user1664043
(379 rep)
Jul 30, 2025, 05:43 PM
Last activity: Aug 1, 2025, 12:51 AM
Last activity: Aug 1, 2025, 12:51 AM