TOP 5 Exciting New Features of SQL Server 2012

Microsoft understands the importance of sql server and is continually striving to make powerful yet easy-to-use tool for Sqlserver developers and end users.
In this Part I will discuss exciting new features related to developer and SQL Server enhancement.

#1 Column Store Indexes
SQL Server 2012 introduced a new feature Column store indexes that can be used to improve query performance from 10x to 100x times. The xVelocity technology makes the columnstore index more efficient than a traditional index.

In a regular index, all indexed data from each row is kept together on a single page, and the data in each column is spread across all pages in an index. In a columnstore index, the data from each column is kept together so that each data page contains data only from a single column. In addition, the indexed data for each column is compressed (means many columns contains repetitive values).The compression ration can be very high and It also reduces the number of pages in memory.

Building a columnstore index is simple. You use the same index creation syntax and specify the keyword COLUMNSTORE. But once you add a columnstore index to a table, the table becomes read-only, so inserts, updates, and deletes are not allowed. If you need to insert or update rows, you can disable the index, make the modifications, and rebuild the columnstore index.

It is greatly reducing I/O and memory utilization on large queries and creating aggregate tables.

Syntax:
CREATE NONCLUSTERED COLUMNSTORE INDEX ON (Col1,Col2........Coln)

Example:
CREATE NONCLUSTERED INDEX INDEX_1 ON EmployeeDetail(ID, Name)

#2 Sequence Objects 
A Sequence is just an object that is a counter same as similar functionality of an identity column, there is an interesting option to utilize called Sequence. Sequence is a user-defined object that creates a sequence number. A good example of its use would be to increment values in a table, based a trigger.

Syntax:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]

[ START WITH ]
[ INCREMENT BY ]
[ { MINVALUE [ ] } | { NO MINVALUE } ]
[ { MAXVALUE [ ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ ] } | { NO CACHE } ]
[ ; ]

• START WITH: Starting number in the sequence
• INCREMENT BY: The incrementing value of the sequence
• MINVALUE: The minimum value the sequence can produce.
• MAXVALUE: The maximum value the sequence can produce.
• CYCLE: If the MAXVALUE is set with the CYCLE, when the MAXVALUE is reached the sequence will cycle to the MINVALUE and start again.
• CACHE: If a CACHE argument is provided, SQL Server will cache (store in memory) the amount of values specified.

#3 Error Handling
If you have programming in languages like C# or other similar languages then you are probably know how to handle errors by using try, catch and throw statements. Transact-SQL also gives you this option to find an exception using a try/catch block.
In SQL Server 2005/2008, RAISERROR has remained the only mechanism for generating your own errors. In SQL Server 2012, a new THROW statement is used to raise exceptions in your T-SQL code instead of RAISERROR.

Example:
We divide a number by zero:

In SQL Server 2005/2008, if you want to re-throw an error in a catch block of TRY CATCH statement, you need to use RAISERROR with ERROR_MESSAGE() AND ERROR_SEVERITY().

BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 12/ 0
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(4000), @ErrorSeverity int
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY()
RAISERROR ( @ErrorMessage, @ErrorSeverity, 1 )
END CATCH

But in SQL Server 2012 you can use only a THROW statement to re-throw an error in a catch block of a TRY CATCH statement.

BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 12/ 0
END TRY
BEGIN CATCH
throw
END CATCH

#4 New T-SQL Functions
There are many new functions added to SQL Server 2012 as shown below

Logical Functions
• CHOOSE (Transact-SQL)
• IIF (Transact-SQL)       ....................................More>>

Conversion Functions
• PARSE (Transact-SQL)
• TRY_PARSE (Transact-SQL)
• TRY_CONVERT (Transact-SQL)   ..........................More>>

Date and time Functions
• DATEFROMPARTS Function
• TIMEFROMPARTS Function
• DATETIMEFROMPARTS Function
• EMONTH Function
....... and so on                     .............................More>>

String Functions
• FORMAT (Transact-SQL)
• CONCAT (Transact-SQL)   ..................................More>>

Analytic Functions
• First_Value Function
• Last_Value Function        ..................................More>>

#5 Pagination
In the earlier versions of SQL Server, if you use a Gridview then you set its pagination property. However if you want to do this from the server side then you need to use the row_number() function and supply the specific range of rows and also retrieve the current page data from the database using a temporary table. The ORDER BY OFFSET & FETCH NEXT ONLY keywords are one of the major features introduced in SQL Server 2012.

Briefly explain as follows

Creating a Table in SQL Server

create table Userdetails
(id int identity (1,1),
firstname varchar(30),
lastname varchar(30))

insert into Userdetails values('John','K')
insert into Userdetails values('Crown','P')
insert into Userdetails values('Lee','B')
insert into Userdetails values('Khan','S')
insert into Userdetails values('Amith','N')
insert into Userdetails values('Nelson','M')
insert into Userdetails values('Maro','T')
insert into Userdetails values('Alijibit','R')
insert into Userdetails values('Francko','S')

select * from Userdetails

The table looks like as follows


To get rows in between 4 to 8

In SQL Server 2008, we have been doing this data paging by writting a complex query as follows.

SELECT *
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY id) AS number, *
FROM Userdetails) AS TempTable
WHERE number > 4 and number <= 8 

 In SQL Server 2012,now using OFFSET and FETCH NEXT keywords to get data paging as follows.

SELECT * 
FROM Userdetails 
ORDER BY id 
OFFSET 4 ROWS
FETCH NEXT 4 ROWS ONLY; 

In the above example, we used OFFSET 4 ROWS, so SQL will skip first 4 records from the result and display the next 4 records in the defined order.