EOMONTH
We have to face problem whenever we are going to identify the end date of month no built in function was there but now that problem has over in SQL Server 2012, EOMONTH return the date of the month
SELECT EOMONTH (‘05/02/2012’) ‘EOM Processing Date
Output: 2012-02-29
You can specify number of month with EOMONTH function also and it can be negative value
SELECT EOMONTH ( Getdate(), -1 ) AS ‘Last Month’
Output: 2012-01-31

CHOOSE
Using this you can find out the 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 string
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.