# Reaping the benefits of the Window functions in T-SQL

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

• 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?

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

• 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.

βWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.β - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• 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".

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

• 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 π

• 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.

• 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!

βWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.β - Gail Shaw

Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

• 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?

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

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

Well done indeed!

Thanks Dwain :Wow:

• 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?

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

• 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! π

• 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!
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

• 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. π

• 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 57 total)