SQL SQL Server 2012 Features
AlwaysOn
Always on feature provide high availability and disaster recovery solution. AlwaysOn maximizes the availability of a set of user databases for an enterprise and improve resource use. SQL Server 2012 provide option to make group set of database called Availability group which will be treated as individual entity.SQL Server 2012 can host multiple availability group. If any failover occur then flexibility to fail over one availability group to instance B, another availability group to instance C, and so on. we don’t have to have a standby server that is capable of handling the full load of our primary server – we can distribute those workloads across multiple, lower-powered servers. These new AlwaysOn features allow us much more power and flexibility in our efforts toward both high availability and disaster recovery, whether we are using Availability Groups or standard Failover Clustering.
Contained Databases, which intends to reduce or eliminate the dependencies that a database has on the SQL Server instance, making it easier to migrate a database to a new instance with less of the work involved in reproducing and validating these dependencies.
ColumnStore Indexes
What a ColumnStore index does is essentially turn a traditional index on its side. Think about a covering index that includes multiple columns, but instead of storing all the different columns in a single row on a single page, split it up and store each column on its own set of pages. With certain types of queries, we experience drastically reduced I/O (even though it sounds like the index will span more pages, not less). Since each column is stored in its own set of pages, this makes compression much more effective.
TRY_CONVERT()
This function quite similar like convert function except it return null value if it can’t convert any value instead of error code .
SELECT CONVERT (INT, 'SSD-416.00’)
Output display Error message but
SELECT TRY_CONVERT (INT, 'SSD-416.00’)
Output display NULL
It will assist to reduce from unexpected termination from execution.
OFFSET / FETCH
Using this feature user can get the flavor or of paging , Offset indicate the initial value from where row will be display and fetch indicate number of rows will be fetch . Offset and fetch facilitate the scrolling data from table . It set after order by clause.
Select * from Employee where departmentCode=’SSD’ order by joiningdate Desc
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY
FORMAT()
SQL Server2012 introduce new date/time function ,using this user can easily generate various day format date, day name ,weekday etc.
DECLARE @birthdate datetime = ’2012-11-05’
Select FORMAT (@birthdate ,N’d’) [Date],
FORMAT (@birthdate ,N’dd’) [Date],
FORMAT (@birthdate ,N’ddd’) [Date],
FORMAT (@birthdate ,N’dddd’) [Date]
Output
11/05/2012 05 Thu Thursday
** **
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