Transact SQL now includes some great new analytical functions, similar to those that exist in MDX. Rather than taking a hard-to-understand dataset from the AdventureWorks
database, let us create a simple one of our own to understand what some of the most useful functions do:
CREATE TABLE Sales
(
SalesYear int,
SalesAmount int
)
INSERT INTO Sales
VALUES (2000, 100),
(2001, 250),
(2002, 300),
(2003, 400),
(2004, 375)
Let us run a quick select statement. The following is what we should see:
Now we will use some f the new functions.
This function, as its name suggests, returns the first value from an ordered set of values. This is really useful as we can calculate, inside a result set, the difference between an initial value and each subsequent value on-the-fly. With no variables and no re-querying, everything is done inside a single piece of Transact SQL:
SELECT SalesYear, SalesAmount, FIRST_VALUE (SalesAmount) OVER (ORDER BY SalesAmount...