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 : 13,337   
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)
13 FEB
ASP.NET Core Development (online)

07:00 AM-09:00 AM IST( MON, WED, FRI)

Know More
25 JAN
Angular2 and Angular1 Development (online)

09:00 PM-10:30 PM IST(MON-FRI)

Know More
24 JAN
ASP.NET MVC with Angular2 Development (online)

07:00 AM-09:00 AM IST(Tuesday & Thursday)

Know More
21 JAN
Xamarin Forms : Build Cross-platform Apps (offline)

05:00 PM-06:30 PM IST

Know More
20 JAN
NODEJS & MEAN Stack 2 Development (online)

09:00 PM-11:00 PM IST( MON, WED, FRI)

15 JAN
PPC Marketing (offline)

04:00 PM-05:300 PM

10 JAN
ASP.NET MVC with AngularJS Development (online)

09:00 PM-11:00 PM IST on (TUE, THRU,SAT)

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

07:00 AM-09:00 AM IST

31 DEC
ASP.NET MVC with AngularJS Development (offline)

09:30 AM-11:00 AM

31 DEC
Angular2 and Angular1 Development (offline)

11:00 AM-12:30 AM

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

03:00 PM-04:30 PM

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

08:00 AM-09:30 AM

4 SEP
MEAN Stack 2 Development (offline)

11:00 AM-12:30 PM.

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

Professional Speaks

+