Yesterday, I gave a webinar for Pragmatic Work's Training on the Ts series. This webinar covered window functions starting with the functionality added with SQL Server 2005. This is also the session I will be giving at the 2013 PASS Summit. There was not enough time for questions during the session, so I am answering them in this blog post. If you missed the session, you can view the recording here.
Q: How different is #preceding and #following from using rank (with rank-1 & rank+1) to get your prior row and follow up rows.
A: I may need more information to answer this one. You use # preceding and # following to define the window. This is called “framing”. The RANK() function will just give you back an integer with the ranking of that value over the window. Please leave a comment if this is not what you meant.
Q: Can you get a random sample from a table rather than only top 100 say?
A: Usually you use TOP along with ORDER BY to get specific rows. If you want the results to be random, ORDER BY NEWID() works pretty well.
Q: How do Common Table Expressions relate to Window Functions?
A: They are two very different things that are often used together. A Common Table Expression or CTE is a virtual table similar a sub-query. They were introduced in 2005. Basically, they allow you to define the virtual table up front, base one CTE on a previous CTE, use the same CTE multiple times in the same query and use recursion. I have written a couple of blogs on CTEs. What is a Common Table Expression and Recursive CTEs.
Window functions are used in the SELECT or ORDER BY clauses. Because the WHERE clause operates before SELECT and ORDER BY, CTEs are often used to allow filtering on the window function results. I had some examples of this during the session when talking about ROW_NUMBER().
Q: Can you pass framing values as a variable to change the Preceding and Following rows?
A: No, only if you build the query dynamically.
Q: Can # PRECEDING OR # FOLLOWING be a variable?
Q: why are these called Window Functions?
A: I will quote from Itzik Ben-Gan’s book “Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions” to give you the answer. Itzik says
“These functions are based on an amazingly profound concept in standards SQL (which is both an ISO and ANSI standard)—the concept of windowing. The idea behind this concept is to allow you to apply various calculations to a set, or window, of rows to return a single value”
Q: Is the Sequence Project Operator just for sql 2012 or previous versions as well?
A: This was introduced in 2005. If you run any query with a window function, you will see this operator in the execution plan.
Q: Would the missing index be suggested in the exec plan? Or it is something we need to find out for ourselves?
A: I am assuming you mean to optimize a window function, using the Partition By + Order By + Covering? I have not seen it suggested. Here is an example where the index would be helpful, and running the query results in an expensive sort:
SELECT SalesOrderID, SalesOrderDetailID,
ROW_NUMBER() OVER(Partition By OrderQty ORDER BY UnitPrice)
When I look at the execution plan, I do not see a recommendation for the index.
Well, those are all the questions I had. Please feel free to post additional questions in the comments.