Reaping the benefits of the Window functions in T-SQL

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    Comments posted to this topic are about the item Reaping the benefits of the Window functions in T-SQL

  • Dwain Camps

    SSC Guru

    Points: 86893

    Interesting article. I'm curious to see what comments Jeff has about it.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • ChrisM@Work

    SSC Guru

    Points: 186107

    The article isn't always easy to follow and might be helped by this:

    DECLARE @pString VARCHAR(8000), @pDelimiter CHAR(1);

    SELECT @pString = 'The,quick,brown,fox', @pDelimiter = ',';

    WITH E1(N) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ), --10E+1 or 10 rows

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

    cteTally(N) AS (SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4),

    cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)

    SELECT 1 UNION ALL

    SELECT t.N+1 FROM cteTally t

    WHERE SUBSTRING(@pString,t.N,1) COLLATE Latin1_General_BIN = @pDelimiter

    )

    SELECT s.N1, L1 = ISNULL(NULLIF((LEAD(s.N1,1,1) OVER(ORDER BY s.N1)-1),0) - s.N1, 8000)

    FROM cteStart s

    -which shows exactly what LEAD() is doing.

    Good solid bit of thinking though, and an excellent enhancement to everybody's favourite string-splitter. Top work.

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Jeff Moden

    SSC Guru

    Points: 996655

    dwain.c (3/24/2014)


    Interesting article. I'm curious to see what comments Jeff has about it.

    Eirikur asked me to review it (seems like a couple/three months ago) and I'm tickled to death about this article coming out for two reasons... it embraces that spirit of continuous improvement/pushing the envelope and I've been dying to talk about the improved method for splitting since my first read of the article but promised not to until the article came out. πŸ˜€

    I will say that I'm ticked off about MS releasing yet another version of SQL Server (2014) without either a splitter function or Tally-Table-like sequence generator function. The timing of this article helps that disappoinment out quite a bit for me.

    Well done, Eirikur!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    ChrisM@Work (3/24/2014)


    The article isn't always easy to follow and might be helped by this:

    DECLARE @pString VARCHAR(8000), @pDelimiter CHAR(1);

    SELECT @pString = 'The,quick,brown,fox', @pDelimiter = ',';

    WITH E1(N) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ), --10E+1 or 10 rows

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

    cteTally(N) AS (SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4),

    cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)

    SELECT 1 UNION ALL

    SELECT t.N+1 FROM cteTally t

    WHERE SUBSTRING(@pString,t.N,1) COLLATE Latin1_General_BIN = @pDelimiter

    )

    SELECT s.N1, L1 = ISNULL(NULLIF((LEAD(s.N1,1,1) OVER(ORDER BY s.N1)-1),0) - s.N1, 8000)

    FROM cteStart s

    -which shows exactly what LEAD() is doing.

    Good solid bit of thinking though, and an excellent enhancement to everybody's favourite string-splitter. Top work.

    Thanks Chris for the comment, still working on my style πŸ˜›

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    Jeff Moden (3/24/2014)


    dwain.c (3/24/2014)


    Interesting article. I'm curious to see what comments Jeff has about it.

    Eirikur asked me to review it (seems like a couple/three months ago) and I'm tickled to death about this article coming out for two reasons... it embraces that spirit of continuous improvement/pushing the envelope and I've been dying to talk about the improved method for splitting since my first read of the article but promised not to until the article came out. πŸ˜€

    I will say that I'm ticked off about MS releasing yet another version of SQL Server (2014) without either a splitter function or Tally-Table-like sequence generator function. The timing of this article helps that disappoinment out quite a bit for me.

    Well done, Eirikur!

    Thank you Jeff, for these kind words and the assistance.

  • ChrisM@Work

    SSC Guru

    Points: 186107

    Eirikur Eiriksson (3/24/2014)


    ChrisM@Work (3/24/2014)


    The article isn't always easy to follow and might be helped by this:

    DECLARE @pString VARCHAR(8000), @pDelimiter CHAR(1);

    SELECT @pString = 'The,quick,brown,fox', @pDelimiter = ',';

    WITH E1(N) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ), --10E+1 or 10 rows

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

    cteTally(N) AS (SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4),

    cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)

    SELECT 1 UNION ALL

    SELECT t.N+1 FROM cteTally t

    WHERE SUBSTRING(@pString,t.N,1) COLLATE Latin1_General_BIN = @pDelimiter

    )

    SELECT s.N1, L1 = ISNULL(NULLIF((LEAD(s.N1,1,1) OVER(ORDER BY s.N1)-1),0) - s.N1, 8000)

    FROM cteStart s

    -which shows exactly what LEAD() is doing.

    Good solid bit of thinking though, and an excellent enhancement to everybody's favourite string-splitter. Top work.

    Thanks Chris for the comment, still working on my style πŸ˜›

    No worries Eirikur, if I could write half as well as you I'd be extremely happy!

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • robert_verell

    Say Hey Kid

    Points: 668

    I like this article if anything for the West Point, MS reference.

  • Dwain Camps

    SSC Guru

    Points: 86893

    Jeff Moden (3/24/2014)


    dwain.c (3/24/2014)


    Interesting article. I'm curious to see what comments Jeff has about it.

    Eirikur asked me to review it (seems like a couple/three months ago) and I'm tickled to death about this article coming out for two reasons... it embraces that spirit of continuous improvement/pushing the envelope and I've been dying to talk about the improved method for splitting since my first read of the article but promised not to until the article came out. πŸ˜€

    I will say that I'm ticked off about MS releasing yet another version of SQL Server (2014) without either a splitter function or Tally-Table-like sequence generator function. The timing of this article helps that disappoinment out quite a bit for me.

    Well done, Eirikur!

    Looks like JM's seal of approval, which doesn't come easily.

    Well done indeed!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    Looks like JM's seal of approval, which doesn't come easily.

    Well done indeed!

    Thanks Dwain :Wow:

  • Dwain Camps

    SSC Guru

    Points: 86893

    Eirikur Eiriksson (3/25/2014)


    Looks like JM's seal of approval, which doesn't come easily.

    Well done indeed!

    Thanks Dwain :Wow:

    You also get my seal of approval. πŸ™‚

    Tally Tables in T-SQL[/url]

    Reference and link at the end of the blog.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    dwain.c (3/27/2014)


    Eirikur Eiriksson (3/25/2014)


    Looks like JM's seal of approval, which doesn't come easily.

    Well done indeed!

    Thanks Dwain :Wow:

    You also get my seal of approval. πŸ™‚

    Tally Tables in T-SQL[/url]

    Reference and link at the end of the blog.

    Thanks Dwain, appreciate it! πŸ˜‰

  • WayneS

    SSC Guru

    Points: 95384

    Eirikur,

    This is an excellent modification to an already excellent function. Very well done!!! (And it was such a simple change at that.)

    Jeff Moden (3/24/2014)


    Eirikur asked me to review it (seems like a couple/three months ago) and I'm tickled to death about this article coming out for two reasons... it embraces that spirit of continuous improvement/pushing the envelope and I've been dying to talk about the improved method for splitting since my first read of the article but promised not to until the article came out. πŸ˜€

    I will say that I'm ticked off about MS releasing yet another version of SQL Server (2014) without either a splitter function or Tally-Table-like sequence generator function. The timing of this article helps that disappoinment out quite a bit for me.

    Well done, Eirikur!

    And now I think I understand why I didn't hear back from Jeff when I sent him some test results of the DelimitedString8K function in 2014... I now need to redo my test with this enhancement. And I'm looking forward to seeing these results.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    WayneS (4/3/2014)


    Eirikur,

    This is an excellent modification to an already excellent function. Very well done!!! (And it was such a simple change at that.)

    Jeff Moden (3/24/2014)


    Eirikur asked me to review it (seems like a couple/three months ago) and I'm tickled to death about this article coming out for two reasons... it embraces that spirit of continuous improvement/pushing the envelope and I've been dying to talk about the improved method for splitting since my first read of the article but promised not to until the article came out. πŸ˜€

    I will say that I'm ticked off about MS releasing yet another version of SQL Server (2014) without either a splitter function or Tally-Table-like sequence generator function. The timing of this article helps that disappoinment out quite a bit for me.

    Well done, Eirikur!

    And now I think I understand why I didn't hear back from Jeff when I sent him some test results of the DelimitedString8K function in 2014... I now need to redo my test with this enhancement. And I'm looking forward to seeing these results.

    Thank you Wayne, much appreciated. 😎

  • GPO

    SSCarpal Tunnel

    Points: 4556

    I'm ticked off about MS releasing yet another version of SQL Server (2014) without either a splitter function or Tally-Table-like sequence generator function

    True, but the workarounds for the abovementioned omissions are nice and fast and easy to understand. Even more of an omission seems to me to be a string concatenation aggregation function. I currently use FOR XML PATH and find it (and xquery more generally) slow, cumbersome and hard to read. Even li'l ol' MySQL has GROUP_CONCAT().

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

Viewing 15 posts - 1 through 15 (of 48 total)

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