Microsoft SQL FIRST_VALUE Microsoft SQL first_value function supported range

Lisa Tai 1 Reputation point
2021-02-25T18:34:50.723+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,579 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Lisa Tai 1 Reputation point
    2021-02-25T18:57:28.72+00:00

    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.

    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2021-02-26T02:32:26.357+00:00

    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.

    0 comments No comments

  3. Lisa Tai 1 Reputation point
    2021-02-26T14:50:15.87+00:00

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