SQL Server 2012 New Features and Programmability Enhancements

SQL Server 2012 New Features: An Overview

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

Read More: SQL Server Interview Questions and Answers

SQL Server 2012 Programmability Enhancements

SQL Server 2012 supports great programmability enhancements and functions that developers and database administrators 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 a throw statement for throwing exceptions in T-SQL. However, this statement has some limitations and does not provide powerful features like RAISERROR.

2. Conversion Functions

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

Read More - Most Asked DBMS Interview Questions

3. T-SQL built-in pagination

SQL Server 2012 also supports built-in pagination with the help of OFFSET and FETCH like MySQL. 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 sequences like Oracle database. It works like an IDENTITY field without being a field. The main advantage of a 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 allows you to determine the shape of projected output from queries, tables, stored procedures, views, and other objects. As a result, SQL Server supports business intelligence and debugging.

6. T-SQL Format() Function

SQL Server 2012 introduces a new function format() for formatting string data like 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, questions, or comments about this article are always welcome.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE