Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 29 November 2012

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

  2. 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.
  3. Conversion Functions

  4. 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.
  5. T-SQL built-in pagination

  6. 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.
    1. --Suppose Employee tables has 500 records
    2. --Below query skip 200 rows and fetch the next 20 records
    3. SELECT EmpID, EmpName, Salary FROM dbo.Employee
    4. ORDER BY EmpID
    5. OFFSET 200 ROWS
    6. FETCH NEXT 20 ROWS ONLY
  7. T-SQL Sequence

  8. 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.
    1. --Create Sequence object
    2. CREATE SEQUENCE objSequence
    3. START WITH 1
    4. INCREMENT BY 1;
    5. --Create Employee object
    6. DECLARE @Employee TABLE
    7. (
    8. ID int NOT NULL PRIMARY KEY,
    9. FullName nvarchar(100) NOT NULL
    10. )
    11. --Insert values
    12. INSERT @Employee (ID, FullName)
    13. VALUES (NEXT VALUE FOR objSequence, 'Mohan'),
    14. (NEXT VALUE FOR objSequence, 'Deepak'),
    15. (NEXT VALUE FOR objSequence, 'Pavan');
    16. --Show data
    17. SELECT * FROM @Employee
  9. 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.
  10. T-SQL Format() Function

  11. SQL Server 2012 introduces a new function format() for formatting string data like as C#. It is a CLR based function.
    1. 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]