The new Analytic functions in SQL Server 2012

Question

The new Analytic functions in SQL Server 2012

Viewing 15 posts - 16 through 30 (of 33 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

j-1064772 (1/19/2012)
How do the newfangled Analytics functions compare with the Quirky Update method used for - among other things - creating running totals in term of performance ?

The Analytic functions don't, but the OVER clause enhancements allows running totals with the SUM() function and the enhanced OVER clause. See my previous article: The OVER Clause enhancements in SQL Server code named “Denali”, CTP3.[/url] You might also want to see my blog post on this: http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/[/url]. In short, the QU still wins, but the newly enhanced OVER clause beats everything else.

Did you click the "TSQL Challenges Winner" icon in my signature? It beat everything else quite handily doing a running total challenge.

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

WayneS SSC Guru Points: 95460 More actions · Answer 2

Zeev Kazhdan (1/19/2012)
Finally they will have what Oracle had delivered ages ago....

and MySql, and DB2, and... well, nearly everyone else. It is long overdue, and should have been in 2008.

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

WayneS SSC Guru Points: 95460 More actions · Answer 3

Jack Corbett (1/19/2012)
Good article Wayne. One thing I like to see in articles like this though is how you might solve the same problem without using the new functions. Just to see how much the new functions help.

Thanks Jack. That is a very good idea... I'll have to keep it in mind.

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

WayneS SSC Guru Points: 95460 More actions · Answer 4

Jason, Geoff, Mark, Tim and KWymore...

Thanks, I'm very glad that you'll like it.

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 5

WayneS (1/19/2012)
Actually...
this article only talks about the Analytic functions. You might want to look at my previous article The OVER Clause enhancements in SQL Server code named “Denali”, CTP3[/url].
Basically, the MIN/MAX functions DO allow you to order your partition by a different column. But let's talk about this "over there"... it's more appropriate there.

Thanks for your link the the other article. I was not aware that these were also upgraded.

Best Regards,

Chris Büttner

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Wonderful article, Wayne. The charts summarize a nicely laid out and well written article and make it real easy to get extra information. Thanks for taking the time to write a class article!

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

Xedni SSCertifiable Points: 6951 More actions · Answer 7

Now if only we could get 2012 on our production boxes before 2020. So excited for SS2012

Executive Junior Cowboy Developer, Esq.[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

Great article Wayne!

Thanks for sharing.

-- Gianluca Sartori

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

Great article Wayne, thanks.

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

zombieisdead2020 SSC-Addicted Points: 414 More actions · Answer 10

I see first_value and last_value function, but do not see second or third value function!

How can I write for example second_value() function by using analytical window function?

WayneS SSC Guru Points: 95460 More actions · Answer 11

zombieisdead2020 (1/29/2013)
I see first_value and last_value function, but do not see second or third value function!
How can I write for example second_value() function by using analytical window function?

You will probably have to revert to the ROW_NUMBER() function, then look for where ROW_NUMBER() = 2, 3, etc.

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

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 12

Wayne,

Excellent job.

Thomas

Thomas LeBlanc, MVP Data Platform Consultant

Alan Burstein SSC Guru Points: 61141 More actions · Answer 13

I just read this article. Great work Wayne.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

WayneS SSC Guru Points: 95460 More actions · Answer 14

Thanks Thomas and Alan!

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 15

First of all Wayne, this is a great primer on using the new SQL 2012 analytic functions, and I know I for one will be referring folks to it if they have any questions.

RichB (1/19/2012)
Any idea what the performance of these things is like?

I can comment on using LAG (vs. LEAD) to calculate gaps. I did a recent test on a 1M row test harness (partitioned sets) and came up with the following results.

MEDIAN CPU (ms) Elapsed (ms)

SQL 2012 LEAD 1747 1740

SQL 2012 LAG 1357 1363

I am not sure why LAG works faster than LEAD but it seemed to be consistent. Neither of them is overall elapsed time winner compared to other solutions I've tried.

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