SQL Server 2012: What’s new in T-SQL?

  • 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 TSQL

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

    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.

  • New Functions in SQL Server 2012

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s