ORDER BY list of RANGE window frame has total size of 1020 bytes. Largest size supported is 900 byte-CodePudding

Here it is some following lines of sql code:

SELECT cd.continent, cd.location, cd.date, cd.population, cv.new_vaccinations,
    SUM(CONVERT(int, cv.new_vaccinations)) OVER (Partition by cd.location
    Order by cd.location, cd.date)
FROM [Project Covid Analysis]..covid_death$ as cd
JOIN [Project Covid Analysis]..Covid_Vaccine$ as cv
    ON cd.date=cv.date 
AND cd.location=cv.location
WHERE cd.continent IS NOT NULL 
ORDER BY 2,3

After execute those lines, I got an error message "ORDER BY list of RANGE window frame has total size of 1020 bytes. Largest size supported is 900 bytes." I think the syntax has already correct. Please, if anybody know how to overcome this. Thanks.

CodePudding user response：

The error message is self-explanatory: you cannot have order by columns in an over clause whose total size exceeds 900 bytes.

Here's an example that replicates your error message:

drop table if exists [dbo].[Example1];
create table [dbo].[Example1] (
  [Location] varchar(1017),
  [Date] date
);
select
  [Location],
  [Date],
  sum(1) over (partition by [Location] order by [Location], [Date]) as [Sum]
from [dbo].[Example1];

-- Msg 8729 Level 16 State 1 Line 6
-- ORDER BY list of RANGE window frame has total size of 1020 bytes. Largest size supported is 900 bytes.

Reduce the size of your Location column so that the total size is less than or equal to 900 bytes, e.g.:

drop table if exists [dbo].[Example2];
create table [dbo].[Example2] (
  [Location] varchar(897),
  [Date] date
);
select
  [Location],
  [Date],
  sum(1) over (partition by [Location] order by [Location], [Date]) as [Sum]
from [dbo].[Example2];

CodePudding user response：

Most likely you didn't want a RANGE UNBOUNDED PRECEDING window frame anyway, but unfortunately that is the default.

Instead use ROWS UNBOUNDED PRECEDING

SUM(CONVERT(int, cv.new_vaccinations)) OVER (Partition by cd.location
    Order by cd.location, cd.date ROWS UNBOUNDED PRECEDING)

Although as noted, if you partition by a value there is no point ordering by it also.