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

---

Error when failing over Availability Group with Listener

The following error shows up in the System event log on the secondary server when trying to fail over an availability group that has a listener configured:

"Cluster network name resource 'resource_name' cannot be brought online. The computer object associated with the resource could not be updated in domain '' for the following reason:

Unable to set DnsHostName attribute.

The text for the associated error code is: A constraint violation occurred.

The cluster identity 'cluster_object' may lack permissions required to update the object. Please work with your domain administrator to ensure that the cluster identity can update computer objects in the domain."

This was due to inadequate permissions for the computer account of the Windows cluster.  The cluster computer object needs the "Read All Properties" and "Create Computer Objects" permission in the domain.  It also needs Full Control to the computer object for the cluster itself.  Once those permissions were granted, the availability group failed over successfully.

Here is the Microsoft article that details the permissions needed.

Create failed for Availability Group Listener

Error when setting up a listener for an availability group for SQL Server AlwaysOn:

"Create failed for Availability Group Listener 'listener_name'.

An exception occurred while executing a Transact-SQL statement or batch.

The WSFC cluster could not bring the Network Name resource with DNS name 'dns_name' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible.  Use a different DNS name to resolve the name conflict, or check the WSFC cluster log for more information.
The attempt to create the netowrk name and IP address for the listener failed.  The WSFC service may not be running or may be inaccessbile in its current state, or the values provided for the network name and IP address may be incorrect.  Check the state of the WSFC cluster and validate the network name and IP address with the network administrator."

This was due to inadequate permissions for the computer account of the Windows cluster.  The cluster computer object needs the "Read All Properties" and "Create Computer Objects" permission in the domain. Once those permissions were granted, the listener was created successfully.

Here is the Microsoft article that details the permissions needed.