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’) ‘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.
SQL:
SELECT BusinessEntityID, 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:
BusinessEntityID SalesYear   CurrentQuota          NextQuota
---------------- ----------- --------------------- ---------------------
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.