I 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.
-
Recent Posts
- Registration is open for PASS Data Community Summit! 2022/04/05
- Intelligent Query Processing Session Follow Up 2020/11/16
- Move a VM with Azure Resource Mover 2020/09/24
- Build Your Brand Presentation 2020/08/18
- Azure Data Studio Notebooks and SQL Prompt 2020/06/04
Categories
- Cloud computing (1)
- Database Administration (7)
- High Availability (1)
- Internals (1)
- Life in IT (32)
- Security (2)
- SQL Server 2016 (3)
- SQL Server Administration (5)
- SSIS (6)
- SSRS (8)
- T-SQL (12)
- T-SQL Window Functions (12)
- Uncategorized (26)
Aunt Kathi's SQL Server Home 
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.
Thank you! I am so glad you enjoyed the book. I am very passionate about SQL Server, I hope that comes through in my writing.
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!? 🙂
Thanks so much! I’ll be looking for that spike in sales!
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!
If you fill out the contact form on the site, I’ll receive the message. Thanks so much for watching my Pluralsight course and buying the book!!
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.
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.
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
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.