Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

T-SQL Window Functions webinar follow up

  • 6 February 2014
  • Author: Kathi Kellenberger
  • Number of views: 6479

For those of you who attended my presentation on Feb. 4th, thank you so much! Here is the recording if you missed it or would like to watch it again. Here are the slides and code

I received a number of questions during the session which I will also answer here:

Q. Any big changes in 2014 in regards to windowing functions.
A. I am sad to say that there are no changes to window functions in 2014 unless Microsoft gives us a last minute surprise.

Q. For the percentile rank function, how does the function know which column/value to evaluate or rank?
A. The ORDER BY option in the OVER clause. Here is the query:

SELECT COUNT(*) NumberOfOrders, Month(OrderDate) AS OrderMonth,
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2007-01-01' AND '2007-12-31'
GROUP BY Month(OrderDate);

Notice that the ORDER BY option is by COUNT(*) in this case. The query is ranking the rows based on this expression.

Q. Can I connect to data that lives on a pre 2012 SQL Server instance and still use the 2012 windowing functions Lag and Lead, for example?
A. If you mean use 2012 SSMS and just connect to an earlier version of SQL Server, then the answer is no. However, if you create a linked server from a 2012 instance to an earlier version it can work. I created a 2008 R2 version and linked to it. I was then able to use the analytic functions using the servername.databasename.schema.table syntax. I don’t necessarily think this is a good idea, though!

Q. Could you please point us to a good resource (book, site, etc.) where we can better enhance our window functions skills?
A. The best resource is Itzik Ben-Gan’s book Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions Also, take a look at my blog. I have 10 or 12 posts just on window functions.

Q. Do the 2012 window functions work on a database in 2008 compatibility mode.
A. Yes, as long as the databases are on a 2012 or 2014 instance.

Q. Earlier in a Demo Example, there was an example of a query performing a function associated with partitions. Is it necessary to define the partition name(s) when including query content in the query?
A. I don’t understand what was asked here. Please feel free to post a comment with more detail.

Q. What’s the difference between FIRST_VALUE/LAST_VALUE and MIN/MAX?
A. MIN returns the minimum value and MAX returns the maximum value. FIRST_VALUE returns the value of any column in the first row of the window. LAST_VALUE returns the value of any column in the last row of the window.

Q. How does FIRST_VALUE and LAST_VALUE determine which row to return?
A. It depends on the window definition in the OVER clause. First of all, you need to look at the ORDER BY option. That is how the data will be sorted. Next, take a look at the PARTITION BY if there is one. The window is divided up into smaller partitions. FIRST_VALUE and LAST_VALUE also support framing. By default, that will be RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That works for FIRST_VALUE, but for LAST_VALUE, it is probably not the frame you meant to use.

Categories: SQL Server
Rate this article:
No rating

Please login or register to post comments.