Post reply

LEAD parameters

  • May 15, 2015 at 12:10 am

    #74792

    Comments posted to this topic are about the item LEAD parameters

  • May 15, 2015 at 12:48 am

    #1798095

    Thanks for this interesting question.

    I am not usually using ranking functions , it is why I attended a session about them during the last SQL Server Days and the speaker gave 3 examples about LEAD which he explained during 15 minutes. So I was able to find the good answer ( I remember that he explained that usually the last parameter is related to the default value . I tried this choice , and that was correct ... ).

  • May 15, 2015 at 3:10 am

    #1798119

    Thanks steve for the questions on LEAD and LAG

  • May 15, 2015 at 5:01 am

    #1798139

    Thanks. As I'm still on 2008, I don't get to play with LEAD and LAG yet. Looking forward to having one soon, but the hardware hasn't arrived yet.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • May 15, 2015 at 10:13 am

    #1798261

    Seems like it's sort of ISNULL function. If it's encountered NULL, the 3rd parameter will replace the entered value. Nice Question.

    Thanks.

  • May 15, 2015 at 10:35 am

    #1798270

    SQL-DBA-01 (5/15/2015)

    Seems like it's sort of ISNULL function. If it's encountered NULL, the 3rd parameter will replace the entered value. Nice Question.

    No, the default parameter of LAG and LEAD is only used when applying the specified offset results in a row outside of the range. If there is an actual row there, the value from that row will always be used, even when it is NULL.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • May 15, 2015 at 6:33 pm

    #1798390

    Nice question. Raises an interesting issue:

    Hugo Kornelis (5/15/2015)

    SQL-DBA-01 (5/15/2015)

    Seems like it's sort of ISNULL function. If it's encountered NULL, the 3rd parameter will replace the entered value. Nice Question.

    No, the default parameter of LAG and LEAD is only used when applying the specified offset results in a row outside of the range. If there is an actual row there, the value from that row will always be used, even when it is NULL.

    Well, BOL explicitly states that the default replaces a NULL in the row found at the offset, but it's trivial to write code which verifies Hugo's statement (actually I only verified it for lag, but ...), so here we have another error in BOL and the BOL error is repeated in this QOTD. But teh BOL page also contains the ludicrous statement "NULL is returned if scalar_expression is nullable or default is set to NULL" :w00t: which suggests that NULL will be returned everywhere a a there is a row at offset if the relevant column is nullable.

    Tom

  • May 17, 2015 at 12:15 am

    #1798486

    patricklambin (5/15/2015)

    Thanks for this interesting question.

    I am not usually using ranking functions , it is why I attended a session about them during the last SQL Server Days and the speaker gave 3 examples about LEAD which he explained during 15 minutes. So I was able to find the good answer ( I remember that he explained that usually the last parameter is related to the default value . I tried this choice , and that was correct ... ).

    +1

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • May 18, 2015 at 1:47 pm

    #1798799

    Ed Wagner (5/15/2015)

    Thanks. As I'm still on 2008, I don't get to play with LEAD and LAG yet. Looking forward to having one soon, but the hardware hasn't arrived yet.

    I'm in the same boat as you.

  • May 18, 2015 at 1:53 pm

    #1798803

    Iwas Bornready (5/18/2015)

    Ed Wagner (5/15/2015)

    Thanks. As I'm still on 2008, I don't get to play with LEAD and LAG yet. Looking forward to having one soon, but the hardware hasn't arrived yet.

    I'm in the same boat as you.

    I'm looking forward to LEAD and LAG. There's a number of performance comparisons I'd like to do against techniques I use now.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • May 21, 2015 at 3:17 am

    #1799578

    Easy one, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • May 21, 2015 at 3:32 am

    #1799582

    Hugo Kornelis (5/15/2015)

    SQL-DBA-01 (5/15/2015)

    Seems like it's sort of ISNULL function. If it's encountered NULL, the 3rd parameter will replace the entered value. Nice Question.

    No, the default parameter of LAG and LEAD is only used when applying the specified offset results in a row outside of the range. If there is an actual row there, the value from that row will always be used, even when it is NULL.

    That's good to know. I think I have to put ISNULL in some of my scripts now 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • May 29, 2015 at 12:04 am

    #1801470

    Nice question, learned something new.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply