Microsoft SQL Server 2012 New Functions
EOMONTH
We had a problem whenever we wanted to identify the end date of a month. There was no built in function. But now that problem is solved in SQL Server 2012. The function EOMONTH returns the date of the month.
SELECT EOMONTH('05/02/2012') AS 'EOM Processing Date'
Output: 2012-02-29
You can specify a number of months in the past or future with the EOMONTH function.
SELECT EOMONTH ( Getdate(), -1 ) AS 'Last Month'
Output: 2012-01-31
CHOOSE
Using this to select a specific item from a list of values.
SELECT CHOOSE ( 4, 'CTO', 'GM', 'DGM', 'AGM', ’Manager’ )
Output: AGM
CONCAT
This function is concatenating two or more strings
SELECT CONCAT( emp_name,'Joining Date', joingdate)
Output: Rahman Joining Date 01/12/2001
LAST_VALUE and FIRST_VALUE
Using the function you can last value among the set of ordered values according to specified ordered & partitioned criteria. First value return the first value in an ordered set of values.
INSERT INTO result(Department ,ID ,Marks ) VALUES (1,103,70), (1,104,58) (2,203,65) (2,201,85)
SELECT Department,Id ,Marks, LAST_VALUE(Marks) over (Partition by Department ORDER BY Marks) AS
'Marks Sequence' ,FIRST_VALUE (Marks) over (Partition by Department ORDER BY Marks) as ‘First value’
FROM result
OutPut
Department Id Marks Marks Sequence First value
1 104 58 58 58
1 103 70 70 58
2 203 65 65 65
2 201 85 85 65
LEAD
Using the function you can accesses data from a subsequent row in the same result set without the use of a self-join.
SELECT EntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');
OutPut
EntityID SalesYear CurrentQuota PreviousQuota
---------------- ----------- --------------------- ---------------------
275 2005 367000.00 556000.00
275 2005 556000.00 502000.00
275 2006 502000.00 550000.00
275 2006 550000.00 1429000.00
275 2006 1429000.00 1324000.00
275 2006 1324000.00 0.00
File Group Enhancement:
A FILESTREAM filegroup can contain more than one file. For a code example that demonstrates how to create a FILESTREAM filegroup that contains multiple files.