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 Friday, January 20, 2012 9:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1239599
Posted Saturday, January 21, 2012 4:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:44 PM
Points: 90, Visits: 300
Now if only we could get 2012 on our production boxes before 2020. So excited for SS2012

Executive Junior Cowboy Developer, Esq.
Post #1239874
Posted Sunday, January 22, 2012 4:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 5,019, Visits: 10,559
Great article Wayne!
Thanks for sharing.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1239970
Posted Tuesday, January 24, 2012 6:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 13,731, Visits: 10,688
Great article Wayne, thanks.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1240802
Posted Tuesday, January 29, 2013 12:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, September 22, 2013 3:29 AM
Points: 20, Visits: 77
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?
Post #1413231
Posted Tuesday, January 29, 2013 9:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 6,601, Visits: 8,902
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
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 #1413429
Posted Thursday, December 26, 2013 8:08 AM


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: Yesterday @ 9:48 PM
Points: 3,099, Visits: 788
Wayne,

Excellent job.

Thomas


Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Post #1525971
Posted Friday, December 27, 2013 9:55 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:54 AM
Points: 581, Visits: 2,709
I just read this article. Great work Wayne.

-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1526211
Posted Friday, December 27, 2013 3:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 6,601, Visits: 8,902
Thanks Thomas and Alan!

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 #1526255
Posted Wednesday, January 1, 2014 8:59 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: Yesterday @ 8:29 PM
Points: 3,648, Visits: 5,322
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1526973
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse