Microsoft SQL FIRST_VALUE Microsoft SQL first_value function supported range

Question

Dear Microsoft SQL Support Representative, I was running a first_value analytical function that functioned perfectly. Then when I re-ran the analytical function I received this error message: Msg 8729, Level 16, State 1, Line 1592 ORDER BY list of RANGE window frame has total size of 8020 bytes. Largest size supported is 900 bytes. Please advise. Could the software be corrupted? Thank you.

Answer 1

Hi GuoxiongYuan,
Thank you for responding I figured out what was missing. The rows between unbounded preceding and unbounded following section of the code was chopped off in the first_value function.

Thank you.

Answer 2

Hi @Lisa Tai ,

Welcome to Microsoft Q&A!

You're getting the message because SQL Server has no way of "knowing" how large the result of a FIRST_VALUE call can be and so is making the pessimistic assumption that it may be up to 8000 bytes.

Besides, 900 bytes limitation is kicked in because that's the largest key size supported by SQL Server needs to perform sorting, which it may have to do to satisfy this part of the query.

You could refer below methods to fix this issue.

• Adding rows between unbounded preceding and unbounded following section after order by statement as you mentioned.

Example:

create table test (  
A int ,  
B varchar(8000)  
)  
insert into test values (1,'nn')  
  
select FIRST_VALUE(A) over (partition by B order by B) from test   
--Msg 8729, Level 16, State 1, Line 8  
--ORDER BY list of RANGE window frame has total size of 8000 bytes. Largest size supported is 900 bytes.  
  
select FIRST_VALUE(A) over (partition by B order by B ROWS UNBOUNDED PRECEDING) from test   
--execute sucessfully  

-Decrease the defined max size of column which is used for order by.
Example:

create table test1 (  
A int ,  
B varchar(900)  
)  
insert into test1 values (1,'nn')  
  
select FIRST_VALUE(A) over(partition by B order by B) from test1   
--Execute successfully.  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thank you Melissa, however my current web site is not allowing me to accept your answer. I do accept your answer.