New Book on T-SQL Window Functions!

window book coverI am very pleased to announce that my new book “Expert T-SQL Window Functions in SQL Server” is now available! This book covers everything I talk about in my two window function presentations plus even more material. I am also thrilled that BI Guru Clayton Groom wrote Chapter 9 of the book containing even more real world examples. I believe that  T-SQL enhancements don’t get the press and excitement that they deserve. I hope that by writing this book, I can help spread the word about the most useful features added to T-SQL in more than a decade.

About Kathi Kellenberger

I am a SQL Server professional working for Linchpin People. I love talking about SQL Server to anyone who will listen, just ask my two year old granddaughter. I love to write and teach. I am so humbled and honored to say that I made SQL Server MVP in 2013.
This entry was posted in T-SQL Window Functions. Bookmark the permalink.

13 Responses to New Book on T-SQL Window Functions!

  1. I love this book and am very happy to have found you have more to offer… I am anxious to read this blog.. If its like the book I have found an great resource.

    Thanks again.

  2. EWAGNER says:

    I won this book at your presentation during the 2015 Kansas City SQL Saturday. It is excellent and I want to say thanks again! Now when I see a query that can be simplified with a Window function I recommend your book. Have you seen the spike in sales yet!? 🙂

  3. Larry Weismantel says:

    Hi Kathii. I was exposed to your material for the first time with a Pluralsight class on Window Functions and learned so much that I purchased your “Expert T-SQL Window Functions in SQL Server” book to learn as much as possible. I have a question for you on the book and am wondering your preferred method of contact.

    Thank you!

  4. J. Hogue says:

    Based on a pleasant introduction from your book way back on SQL Server 2000, I had no hesitation buying your new book on windowing functions.

    First the flowers: right up front, a tremendous motivation for the newfangled windowing functions’advantage over using a derived table from a sub-query.

    Now, the pot.
    One thing that puzzles me is your caveat on page 10 that the non-deterministic ROW_NUMBER function cannot be turned into a deterministic function, i.e. the sequence is not guaranteed to be absolutely repeatable.

    Given the big deal made about using the ORDER BY clause inside the OVER clause I am somewhat astonished that the sequence numbering cannot be made consistent. “…You cannot influence the determinism of any built-in function…”.

    I would understand if the column in the ORDER BY clause does not have UNIQUE ENTRIES (such as multiple repetitions of a CustomerID in a SalesOrderHeader table where the same customer may have multiple orders).

    But if ordering by a column which absolutely has unique entries (such as a SalesID) does not change the non-deterministic nature of the ROW-NUMBER function, then what is the point ?

    Regards

    • It’s not possible to “change” a non-deterministic function into a deterministic function. You can get repeatable results by using a unique ORDER by in the OVER clause, but the classification of the function has not changed.

  5. J. Hogue says:

    I am getting confused with your test on pages 10 and 18 stating that the ROW_NUMBER function is non-deterministic and its behavior cannot be changed while at the same time mentioning that choosing a column with unique values in the OVER clause will make the ordering repeatable.

    If there is a way to force the non-deterministic function to return repeatable results (ordering) than it stops behaving in a non-deterministic way.

    • By using a unique order by in the OVER clause, you will get consistent results because the values are unique. It is still a non-deterministic function by definition, though.

  6. J. Hogue says:

    Hit a snag at your example 8-1.1 Using PERCENT_RANK and CUME_DIST.

    For order month 5 (428 orders) the rank 3 -1 over the number of ranks 12 -1 yields 0.1818 for the PERCENT_RANK. This matches what I understand from the description of the function given in your book.

    However, in the case of CUME_DIST, the rank 3 over the number of ranks 12 should be 0.250
    (3 / 12).. Both my own SQL Serve and your book concur that the result is 0.333 !

    Could you please elaborate a bit further on how CUME_DIST is calculated in the case of ties (all the other results match with my understanding).

    Regards

    • Thanks for reading the book! Cumulative distribution is more complicated than just ranking, and, since I don’t have a good background in statistics, I don’t understand it well. You can take a look at this to see more https://en.wikipedia.org/wiki/Cumulative_distribution_function In the book, I used the definition found here https://msdn.microsoft.com/en-us/library/hh231078.aspx, and the math doesn’t work out to what would be intuitive. Thanks,Kathi

      Date: Tue, 31 May 2016 20:07:31 +0000 To: kathi.kellenberger@outlook.com

      • J. Hogue says:

        If there is one thing that is not intuitive, Statistics is it. Glad to know I did not go off the rails calculating the cumulative distribution.
        As you can see by the detail I am going through, I think your book is really worth the attention I am giving it. Gave it a 5-star rating on Amazon.
        The subscriptions problem is up to now (end of chapter 8) the best illustration of the power of CTE – windowing functions duo. All the more so when I looked at SS2K8 solutions of the SQL Speed Phreakery contest. The following article was most welcome to explain the winner’s solution – that was something I doubt I would ever have been to develop on my own.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s