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.
Richard Mueller edited Revision 1. Comment: Worked on the grammar, fixed a tag so it reads "SQL Server 2012" and "SQL Server Release Notes"
Maheshkumar S Tiwari edited Original. Comment: Added Tag