- Sequence object
Sequence object is similar to identity property of a column with only difference being we can use its value across multiple table’s.We can also create cyclic sequence object and play a lot with this new feature of SQL Server 2012.
- PAGING windows in T–SQL
In SQL Server 2008 R2,there was not much built in functionality available for developers for paging especially required by UI developers.But SQL Server 2012 has made available this functionality by providing Offset Fetch next keywords which can be used after Order by clause.
- Exception Handling in SQL Server 2012
- New Functions in SQL Server 2012
In SQL Server 2008 R2 it was a best practice to implement exception handling using a TRY/CATCH block. SQL Server 2012 allows you to implement a THROW exception that will be used in conjunction with the TRY/CATCH block and THROW the exception as it was caught by the CATCH block.
Some of the new functions introduced in SQL Server 2012:
1. String Manipulation using CONCAT:
CONCAT function concatenates 2 or more values together into a single string expression. NULL values by default will not impact the result set and will be considered as empty values when using in combination with the CONCAT function
- Example, SELECT CONCAT ( ‘DBA’, ‘Mentalist’ ) AS Result;
2. FORMAT function:
Specified format and optional culture or language setting.The FORMAT function returns a value formatted with a FORMAT ( value, format [, culture ] )
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, ‘dd/MM/yyyy’, ‘en-US’ ) AS Result;
3.Logical functions IIF and CHOOSE
The IIF statement is a shorter way to write a case statement in which only one or another value will be returned base on that the Boolean expression defined.
DECLARE @a int = 10;
DECLARE @b int = 20;
SELECT IIF ( @a < @b, ‘TRUE’, ‘FALSE’ ) AS Result;
The CHOOSE statement returns an item, specified based on the index from a list of values.
SELECT CHOOSE ( 3, ‘PM’, ‘TL’, ‘Developer’, ‘Tester’ ) AS Result;
4.TRY_CONVERT function in SQL Server 2012
TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.