Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

The new Analytic functions in SQL Server 2012 Expand / Collapse
Author
Message
Posted Thursday, January 19, 2012 1:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:39 AM
Points: 101, Visits: 489
How do the newfangled Analytics functions compare with the Quirky Update method used for - among other things - creating running totals in term of performance ?
Post #1239031
Posted Thursday, January 19, 2012 1:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:11 AM
Points: 13, Visits: 181
Thanks - this is a great summary.
Post #1239034
Posted Thursday, January 19, 2012 3:27 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 10:45 AM
Points: 3,266, Visits: 1,959
Nice article, very helpful. It's nice to see articles like this with examples. Definitely helps to prove to others why upgrades are needed. :)
Post #1239122
Posted Thursday, January 19, 2012 7:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 6,582, Visits: 8,864
Christian Buettner-167247 (1/19/2012)
WayneS (1/18/2012)
Comments posted to this topic are about the item <A HREF="/articles/SQL+Server+2012/76704/">The new Analytic functions in SQL Server 2012</A>

Great information, thanks for that.

To me it was not clear immediately, what the difference between the MAX Aggregate Window Function and LAST_VALUE(X) was. In the end
MAX(x) OVER (PARTITION BY y)

should return the same as
FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY X DESC)

But obviously, the MAX / MIN Aggregate Window Functions do not allow you to order your partition by a different column before applying the aggregate. (And it actually does not really make sense to impose a different order on MAX or MIN.)
So the whole point of FIRST_VALUE and LAST_VALUE seem to be:
FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY SomeOtherColumn DESC)

Any other points I may have missed?


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.

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.


Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1239172
Posted Thursday, January 19, 2012 7:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 6,582, Visits: 8,864
RichB (1/19/2012)
Any idea what the performance of these things is like?


Did you click on that "TSQL Challenges Winner" icon in my signature? (Well, that is using the aggregate functions and not the analytic, but I believe them to be similar.)


Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1239173
Posted Thursday, January 19, 2012 7:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 6,582, Visits: 8,864
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. You might also want to see my blog post on this: http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/. 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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1239174
Posted Thursday, January 19, 2012 7:48 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 6,582, Visits: 8,864
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1239175
Posted Thursday, January 19, 2012 7:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 6,582, Visits: 8,864
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1239176
Posted Thursday, January 19, 2012 7:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 6,582, Visits: 8,864
Jason, Geoff, Mark, Tim and KWymore...

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


Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1239177
Posted Friday, January 20, 2012 12:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
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.

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
Post #1239219
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse