Reaping the benefits of the Window functions in T-SQL

Question

Reaping the benefits of the Window functions in T-SQL

Eirikur Eiriksson

SSC Guru

Points: 182881
More actions
March 23, 2014 at 5:00 am

#291268

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

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

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

Dwain Camps SSC Guru Points: 86908 More actions · Answer 1

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: 186127 More actions · Answer 2

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

), --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}

For fast, accurate and documented assistance in answering your questions, please read this article.
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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

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.

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

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 4

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: 182881 More actions · Answer 5

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: 186127 More actions · Answer 6

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}

For fast, accurate and documented assistance in answering your questions, please read this article.
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

robert_verell Say Hey Kid Points: 668 More actions · Answer 7

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

Dwain Camps SSC Guru Points: 86908 More actions · Answer 8

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: 182881 More actions · Answer 9

Looks like JM's seal of approval, which doesn't come easily.
Well done indeed!

Thanks Dwain :Wow:

Dwain Camps SSC Guru Points: 86908 More actions · Answer 10

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: 182881 More actions · Answer 11

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: 95409 More actions · Answer 12

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: 182881 More actions · Answer 13

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: 4574 More actions · Answer 14

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