Since 2003 in the SQL ANSI/ISO standard and extended in 2008. Microsoft implemented the first window functions in their SQL Server 2005 version (known as OVER clause) and extended them in the following versions.
So to be short no they are not new for the ANSI/ISO standard and for Microsoft SQL Server window functions exist for a few version now. Within in Dynamics Ax we use X++ which is translated by the Dynamics Ax Kernel to T-SQL to make the database return a result set. This makes us depended of the implementation of X++, which in Dynamics Ax 2012 doesn’t support window functions for select statements etc. But I can show you a way on how to use them in AX 2012 and up.
Let me show you with an example. As this is a blogpost for Dynamics Ax minded people let us use the SalesLine table.
If you want to select a few fields of this table, you would write something like the following (in SQL and X++):
If you would like to see the total amount of the salesorder in the same result set you would need to do a “GROUP BY”:
in X++ you would do something like this:
But it will send a statement for each salesline to the database.
A better way to do this in Ax would actual be:
Now let’s make it even more interesting. I would like a statement to get a resultset with the following columns:
And here we are, our first window functions. They are called window functions because they put a window (subset) over the rows you get out of your FROM clause.
The <window function> part can be: Ranking functions, Aggregate functions or Analytic functions.
After the OVER keyword you can limit the size of the window:
How to use these powerful window functions in Dynamics AX?
As I mentioned before X++ isn’t a real options. But in the View objects in the AOT there is a little gem that we can polish for our purpose.
We changed the create statement of the view to include ‘SUM(LINEAMOUNT) OVER (PARTITION BY SALESID)’ as a column. By synchronizing we created a view in SQL Server that contains the window functions we want to.
Yes you can use all possibilities of the T-SQL language here. But remember you are changing the Select clause of the view here. Putting a select statement in here (or a stored procedure) will for sure make the view run a long time as every row will trigger a separate statement. But a scalar value function can be very valuable sometimes (like ISNULL()).