Please enable Javascript to correctly display the contents on Dot Net Tricks!

SQL Server 2012 New Features and Programmability Enhancements

  Author : Shailendra Chauhan
Updated On : 24 Sep 2016
Total Views : 12,697   
Support : SQL Server 2012
 

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
    ORDER BY EmpID
    OFFSET 200 ROWS
    FETCH NEXT 20 ROWS ONLY 
  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
    START WITH 1
    INCREMENT BY 1;
     --Create Employee object
    DECLARE @Employee TABLE
    (
    ID int NOT NULL PRIMARY KEY,
    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.

YOU MIGHT LIKE
Free Interview Books
 
COMMENTS (0)
14 DEC
ASP.NET MVC with AngularJS Development (online)

MON-FRI 07:30 AM- 09:00 AM IST

Know More
11 DEC
ASP.NET MVC with AngularJS Development (offline)

Sat-Sun 09:30 AM-11:00 AM IST

Know More
5 DEC
AngularJS Development (online)

Mon - Fri     6:30 AM-7:30 AM IST

3 DEC
AngularJS Development (offline)

SAT,SUN     11:00 AM-12:30 PM IST

3 DEC
MEAN Stack Development (offline)

Sat, Sun     (11:00 AM-12:30 PM IST)

26 NOV
ASP.NET MVC with AngularJS Development (offline)

(SAT,SUN)     03:30 PM-05:00 PM IST

24 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     09:30 PM-11:00 PM IST

12 NOV
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN     08:00 AM-09:30 AM

3 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     07:30 AM-09:00 AM IST

25 OCT
.NET Development (offline)

Mon-Fri     9:00 AM-11:00 AM IST

BROWSE BY CATEGORY
 
RECENT ARTICLES
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+