SQL row size and window functions

Transact-SQL
1

Hello. English in not my native language, so sorry. But

There is a complex resulting SQL query, which is essentially a report that calculates and aggregates values like

SELECT RowNumber, column2, column3,
	CASE Calculated WHEN 0 THEN T1.[Today] 
	ELSE
		CASE RowNumber
			WHEN 7 THEN CAST(LAG([Today], 4) OVER (ORDER by RowNumber) / NULLIF(LAG([Today], 1) OVER (ORDER by RowNumber) , 0) * 100 AS DECIMAL(20,8)) 
                         ... another similar WHEN rows ...
		END
	END AS [Today] ,
        ... more columns like previous ...
	FROM ...main query...

There are about ten top-level columns in top, and they are almost all numeric, i.e. exceeding the length of one line should not be here.
However, for a large number of rows, the query execution fails with an error

The query processor is unable to produce a plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query.

According to the results of the research, it turned out that only the WHEN conditions in which the LAG and LEAD functions are present affect the size of the row. Although, according to common sense, for each single row with its own RowNumber, only one match to the WHEN condition should be selected, and the rest should be ignored. But no, somehow the length of the row grows exactly in proportion to the number of WHENs in which there are window functions, even if the RowNumber, which is the WHEN condition, does not match for these rows. And the row with the number RowNumber = 1 falls due to exceeding the length somewhere in the CASE RowNumber construct. What's even more crazy - the fall comes even faster (with fewer WHENs) if you specify a default value as the third parameter in window functions.

Question - does anyone understand how SQL works with these window functions? Why do they increase the length of the row even in those cases when they should not be executed at all (cut off by a WHEN mismatch)?

2

Have you tried removing columns from the SELECT clause to see if the query runs without the error? That would be my first suggestion.

You said the columns are "almost all numeric", are there any columns that are varchar(8000) or varchar(max) or nvarchar(max)? If you do, I'd suggest removing them from the SELECT clause too.

If you still get the error after removing columns, you'll need to post the full query, and if possible, an estimated query plan if you can't generate an actual plan. It would also help to post the CREATE TABLE statements of the tables used in the query.

3

Ok, i'll try to prepare both table & statement.
No, there are no nvarchar(8000) and only one nvarchar at all.
Yes, when I removed some calculated DECIMAL columns from SELECT - the row size decreases and the error goes away. The same thing happens if I replace LAG/LEAD with any other expression.

4

Suggest you add back 1 column/expression at a time until you get the error, and also check the execution plan when it succeeds. See how many spools it creates.

5

I will take a SWAG at what is happening. We all understand that SQL Server stops processing a case expression as soon as a match is found - but the problem here is that we don't know which path is going to be valid for any given row.

So - when the worktable is generated it must account for all possible paths through all case expressions and that generates the error you are getting. The worktable isn't structured the same as the results - it is going to be structured however SQL needs it to be structured to allow the system to evaluate the expression.

I think your only option here is going to be rewriting the query. How that needs to be done isn't clear - but any opportunities to DRY the code will definitely help. It may also be improved using a divide & conquer approach - building intermediary temp tables.

If that is something you want help with - then you would need to provide the full query, sample data and expected results.