T-SQL Enhancements in SQL Server 2012

There are a number of new T-SQL enhancements added in SQL Server 2012.  The video below covers the following new objects, functions, and options:

SEQUENCE object

Logical Functions:
CHOOSE
IIF

Date and Time Functions:
DATEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS
SMALLDATETIMEFROMPARTS
TIMEFROMPARTS
EOMONTH

OFFSET and FETCH

Window Functions:
FIRST_VALUE
LAST_VALUE
LAG
LEAD
CUME_DIST
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC

Below are SQL statements used in the video:

---

/*********************************************
 SEQUENCE Object
*********************************************/

CREATE SEQUENCE MySeq1
 START WITH 1
 INCREMENT BY 1

SELECT * FROM sys.sequences

SELECT NEXT VALUE FOR MySeq1

ALTER SEQUENCE MySeq1
 RESTART WITH 2
 INCREMENT BY 2
 MINVALUE 2
 MAXVALUE 10
 CYCLE

CREATE TABLE TestTable1
(
 SequenceNo int,
 ItemName varchar(50)
)

INSERT INTO TestTable1 VALUES (NEXT VALUE FOR MySeq1, 'Item1')

SELECT * FROM TestTable1

DROP TABLE TestTable1
DROP SEQUENCE MySeq1


/*********************************************
 Logical Functions: CHOOSE and IIF
*********************************************/

SELECT CHOOSE(2, 'Peter', 'Paul', 'Mary')
SELECT IIF(1=2, 'Apples', 'Oranges')


/*********************************************
 Date and Time Functions
*********************************************/

/*
DATEFROMPARTS (year, month, day)
DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
EOMONTH (start_date [, month_to_add])
*/

SELECT DATEFROMPARTS (2012, 8, 4)
SELECT DATETIMEFROMPARTS (2012, 2, 20, 14, 30, 5, 10)

DECLARE @start_date datetime
SET @start_date = '8/22/12'
SELECT EOMONTH (@start_date)
SELECT EOMONTH (@start_date, 2)
SELECT EOMONTH (@start_date, -1)


/*********************************************
 OFFSET and FETCH
*********************************************/

SELECT * FROM Production.Product ORDER BY ProductID

SELECT * FROM Production.Product ORDER BY ProductID OFFSET 10 ROWS
FETCH NEXT 25 ROWS ONLY


/*********************************************
 Window Functions
*********************************************/

/*
FIRST_VALUE - Returns the first value in an ordered set of values
LAST_VALUE - Returns the last value in an ordered set of values
LAG - Returns the value of the previous row in an ordered set
LEAD - Returns the value of the next row in an ordered set
CUME_DIST - Calculates the cumulative distribution of a value in a group of values
PERCENT_RANK - Gives the rank of a row relative to all the rows in the partition
PERCENTILE_CONT - Calculates a percentile based on a continuous distribution of the column value
PERCENTILE_DISC - Returns the smallest CUME_DIST value that is greater than or equal to a specified percentile
*/

--DROP TABLE TestTable2
CREATE TABLE TestTable2
(
 SaleDate datetime,
 CustomerID int,
 SalePrice float
)

INSERT INTO TestTable2 VALUES
 ('2012-01-03', 395, 81),
 ('2012-01-08', 550, 62),
 ('2012-01-22', 244, 130),
 ('2012-02-11', 395, 17),
 ('2012-02-14', 100, 45),
 ('2012-03-17', 484, 20),
 ('2012-03-31', 100, 33),
 ('2012-04-02', 244, 59),
 ('2012-04-26', 395, 192),
 ('2012-05-12', 100, 20),
 ('2012-05-18', 395, 114)

SELECT * FROM TestTable2

-- FIRST_VALUE, LAST_VALUE, LAG, LEAD
SELECT
 SaleDate,
 CustomerID,
 SalePrice,
 DateOfLowestSale = FIRST_VALUE(SaleDate) OVER (PARTITION BY CustomerID ORDER BY SalePrice),
 DateOfHighestSale = LAST_VALUE(SaleDate) OVER (PARTITION BY CustomerID ORDER BY SalePrice
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
 PreviousSaleDate = LAG(SaleDate, 1) OVER (PARTITION BY CustomerID ORDER BY SaleDate),
 NextSaleDate = LEAD(SaleDate, 1) OVER (PARTITION BY CustomerID ORDER BY SaleDate)
FROM
 TestTable2
ORDER BY
 SaleDate

-- CUME_DIST, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC
SELECT
 SaleDate,
 CustomerID,
 SalePrice,
 CumeDist = CUME_DIST() OVER (PARTITION BY CustomerID ORDER BY SalePrice),
 PctRank = PERCENT_RANK() OVER (PARTITION BY CustomerID ORDER BY SalePrice),
 PctCont = PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SalePrice)
  OVER (PARTITION BY CustomerID),
 PctDisc = PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY SalePrice)
  OVER (PARTITION BY CustomerID)
FROM
 TestTable2
ORDER BY
 SaleDate

---