SQL Server 2012 New Features and Programmability Enhancements

Shailendra Chauhan  Print   3 min read  
02 Oct 2012
06 Aug 2018

SQL Server 2012 has a powerful set of fetures to increase productivity of developers and database administrators. SQL Server 2012 has a new SSMS that is built on Visual Studio 2010 and support various productivity features of Visual Studio 2010 like multi monitor support and pull out the tabs from main work area to different monitor or out side the IDE. Now, you can also use Visual Studio short-cut keys with SQL Server 2012. More over SQL Server 2012 supports business intellisense and debugging like Visual Studio 2010.

SQL Server 2012 Programmability Enhancements

SQL Server 2012 supports a great programmability enhancements and functions that developers and database adminsitrators will enjoy. Some new programmability enhancements and functions are listed below:

  1. T-SQL THROW Statement

    Now SQL Server 2012 has improved error handling by introducing throw statement for throwing exception in T-SQL. However this statement has some limitations and does not provide powerful features like RAISERROR.

  2. Conversion Functions

    SQL Serve 2012 has support for new conversion functions TRY_CONVERT, and TRY_PARSE for convering numeric, date, and time values to desired format. These functions are very helpful while programming T-SQL.

  3. T-SQL built-in pagination

    SQL Server 2012 also supports built-in pagination with the help of OFFSET and FETCH like MySQL. Actually, OFFSET and FETCH will act as a replacement for TOP in SQL Server.

     --Suppose Employee tables has 500 records
    --Below query skip 200 rows and fetch the next 20 records
    SELECT EmpID, EmpName, Salary FROM dbo.Employee
  4. T-SQL Sequence

    SQL Server 2012 also supports sequence like Oracle database. It works like as IDENTITY field without being a field. The main advantage of sequence is that you can use it in the same way as GUIDs and with better performance than GUIDs when used as a clustered index key.

     --Create Sequence object
    CREATE SEQUENCE objSequence
     --Create Employee object
    DECLARE @Employee TABLE
    FullName nvarchar(100) NOT NULL
     --Insert values
    INSERT @Employee (ID, FullName)
    VALUES (NEXT VALUE FOR objSequence, 'Mohan'),
    (NEXT VALUE FOR objSequence, 'Deepak'),
    (NEXT VALUE FOR objSequence, 'Pavan');
     --Show data
    SELECT * FROM @Employee 
  5. Metadata Discovery Enhancements

    SQL Server 2012 also has improved metadata discovery, which allow you to determine the shape of projected output from queries, tables, stored procedure, views, and other objects. As a result SQL Server supports business intellisense and debugging.

  6. T-SQL Format() Function

    SQL Server 2012 introduces a new function format() for formatting string data like as C#. It is a CLR based function.

     SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS [ISO Formatted Date], FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss') AS [Full ISO], FORMAT(GETDATE(), 'MMMM dd, yyyy') AS [Long-EN Date], FORMAT(22.7, 'C', 'en-US') AS [US Currency], FORMAT(99 * 2.226, '000.000') AS [Padded Decimal], FORMAT(12345678, '0,0') AS [Commas in Large Numbers] 
What do you think?

I hope you will enjoy these tricks while programming with SQL Server. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Share Article

Take our free skill tests to evaluate your skill!

In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.

Learn to Crack Your Technical Interview

Accept cookies & close this