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]