What’s New in Transact-SQL Server 2012

In April 2012, Microsoft announced the release of SQL Server 2012. 

So what’s new with T-SQL Server 2012?  Well, there are a lot of new features in the T-SQL Server 2012 editions. In this article we will discuss new features of T-SQL Server 2012.

• Sequence :- It is a new construct to generate sequence numbers.  A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. Sequence can be ascending or descending and can be configured to restart when exhausted.  Future values can be cached by minimizing the disk IO.
  
• Executing Stored Procedure with Result Sets :- It allows you to change the name and data type of columns in the results of the store procedure. Before SQL server 2012, we were doing the same but it was a lengthy procedure. First we had to create a temporary table and then execute the stored procedure and insert the result set in the temporary table and then select it.
                                                
• Offset and Fetch Clause :- It allows you to do server side paging. The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.
  
  
• THROW :-  Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL. It reduces the need of RAISERROR.
  
  
• PARSE :- It attempts to parse string or convert it to specified data type. It can only convert to number or date time. It uses CLR for its operations. If a null constant is passed, an error is raised. A null value cannot be parsed into a different data type in a culturally aware manner If a parameter with a null value is passed at run time, then a null is returned, to avoid canceling the whole batch.
  
  
• TRY_PARSE :- Returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server 2012. Use TRY_PARSE only for converting from string to date/time and number types. This function depends on CLR.
  
  
• TRY_CONVERT :- Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
  
  
• Logical IIF Function :-  Returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server 2012. IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. It has same limitations like CASE statement.
  
  
• CHOOSE  :- It returns a value from specified list based on index.  If the index is not in list, it returns NULL value. It returns the data type based on data type precedence.
   
• EOMONTH :- It returns the last date of specified month. You can optionally specify offset to increment or decrement result.
  
  
•  CONCAT :- Returns a string that is the result of concatenating two or more string values. NULLS are automatically converted to empty strings. It can pass other data type for concatenations.
   
• FORMAT :- It simplifies the string formatting of date or other data types. The functions returns string. It depends on CLR.