What's New in SQL Server 2012

New T-SQL analytical functions

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.

FIRST_VALUE

This function, as its name suggests, returns the first value from an ordered ...