Windows Functions: Tell me when that changes.

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  March 22, 2017 at 1:47 pm

  #1934384

  Luis Cazares - Wednesday, March 22, 2017 1:17 PM

  bc_ - Wednesday, March 22, 2017 12:43 PM

  ChrisM@home - Saturday, March 18, 2017 4:07 PM

  Jeff Moden - Saturday, March 18, 2017 3:31 PM

  ChrisM@Work - Friday, March 17, 2017 10:37 AM

  Whatever did we do without ROW_NUMBER? Oh, I remember now - we ran stuff into a temp table, using the IDENTITY() function in the SELECT. Mimicing PARTITION BY was less trivial. Throw NTILE, SUM, COUNT etc into the mix and you've got a sea-change in the way we code. Magic 🙂

  Partition By was pretty easy to solve once you had things in the Temp Table.  I don't even have to tell you how because you already know.  The reminder is "QU". 😉

  You know that I know that...
  Our BI team had a logic problem last week. I gave them three different solutions to play with, all learned here on ssc. The fastest was a QU solution which ran in moments. They were blown away.

  Could either of you point to an article or description of a "QU solution", please?

  Here's the article explaining it.
  http://www.sqlservercentral.com/articles/T-SQL/68467/

  To Add to Luis' comment. The QU solution is undocumented which is cause for some controversy but plenty safe IMO provided that you follow the Rules detailed towards the end of the article.

  "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