New Features and Programmability Enhancements
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.
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
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.
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
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.
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]
No comments:
Post a Comment