Whenever you are doing any kind of query you must always try and make it a set-based query. Avoid iterating over a query result to get an answer. It is much slower and means writing more SQL. If you really want your SQL queries to fly, open a window. This isn’t a Haiku, so please stay with me.
But I’m using a Mac?
This post is a continuation from a previous post about relation mathematics. Pop over there now if you want a better understanding of where set-based queries come from and why it is important.
Firstly fan boys, windows functions in SQL is not related to Microsoft. They are standard functions introduced with SQL: 2003 which most Relation Database Management Systems (RDMS) support.
Windows functions are almost like group functions. The difference between the two is how the functions are applied. With a group, a SUM function is applied per group and an answer given per group, i.e. a row per group is returned not per underlying row. With a window, a SUM function is applied per framed set of rows, but all underlying rows are still returned.
What that means is that if you have to run a query and return something like running total or a moving average as well, you can use windows functions instead of some iteration logic.
See now do
They say a picture paints a thousand words. Well I’m sure an example paints at least a few hundred. The sample code uses the Adventureworks database from Microsoft and works on SQL Server 2012.
Let me set the scene. You are sitting at your desk, there is a slight hum from the florescent lighting overhead. You get an email from your manager. He needs you need to create a SQL query that returns a list of all the products sold with a running total for each product and each day plus a total for each day and product.
First the iteration solution and a sample of the result. Nothing strange here, just a straight forward while loop reading from a cursor and updating a table variable. On my i5 laptop this took almost 2 minutes to run.
Now, using windows functions. The magical word to use windows functions is OVER. Just a note that each RDMS has a slightly different syntax for this. The code is much more compact and returns a set-based result and that is what we want. The results look the same. The set-based query only took 2 seconds to run.
Another weapon
I’m not going to go into details about the syntax and every different permutation of it as that will just be too boring. I hope I at least got you interested in doing some further investigation of your own. Windows functions are very powerful and once you know about them and you are comfortable to use them, it is a very powerful weapon to add to your problem-solving arsenal.
I’m interested to know how many developers using SQL Server, know about windows functions and have used them. They were only introduced to SQL Server with the release of SQL Server 2012. They have been around a bit longer for DB2 and Oracle.
Also, subscribe to my blog and you won’t miss any future posts.