Click here to Skip to main content
15,860,972 members
Articles / Database Development / SQL Server

Top 20 exciting features of SQL Server 2012 – Part 1

Rate me:
Please Sign up or sign in to vote.
4.85/5 (72 votes)
14 Mar 2013CPOL8 min read 363.9K   145   36
In this article I will discuss 5 features of SQL Server 2012 and the rest will be followed in other parts..

Introduction
Feature number 1 (Revolution):- Column store indexes
Feature number 2 (Evolution):- Sequence objects
Feature number 3 (Revolution):- Pagination
Feature number 4 (Revolution):- Contained database
Feature number 5 (Evolution):- Error handling
Which are the next 5 features?

Introduction

SQL Server 2012 was released on April 2012 and it has started becoming favorite among professionals. Any new product comes from Microsoft the first thing I personally ask myself, is it worth to jump in?. Is it worth to spend customer’s hard earned money to get in to that product?. The way to assess the same is dividing the product features in to   “revolution” and “evolution”. “Revolution” means it’s completely a new thing while “evolution” means there was something already and it has been improvised.

In this article I will discuss 20 features of SQL Server 2012 which I personally like (we can agree to disagree on my list) and from these 20 features, 7 are revolution and 13 are evolution. This article is divided in to 4 parts, in every part we will discuss 5 features. So let’s start with the first 5 top features. 

Image 1

Feature number 1 (Revolution):- Column store indexes

Column store indexes are unexpected and awesome feature. When I read this feature first time I was like, mouth wide open. You can get this feature when you right click on the indexes folder as “Non-Clustered Column store Index” , as shown in the below figure.

Image 2

So let’s quickly understand what exactly it does. Now Relational database store data “row wise”. These rows are further stored in 8 KB page size.

For instance you can see in the below figure we have table with two columns “Column1” and “Column2”. You can see how the data is stored in two pages i.e. “page1” and “page2”. “Page1” has two rows and “page2” also has two rows. Now if you want to fetch only “column1”, you have to pull records from two pages i.e. “Page1” and “Page2”, see below for the visuals.

As we have to fetch data from two pages its bit performance intensive.

Image 3

If somehow we can store data column wise we can avoid fetching data from multiple pages. That’s what column store indexes do. When you create a column store index it stores same column data in the same page. You can see from the below visuals, we now need to fetch “column1” data only from one page rather than querying multiple pages.

Image 4

Feature number 2 (Evolution):- Sequence objects

This feature is good to have and I personally feel it just mimics Oracle’s sequence objects.  Looks like it’s just a good to have feeling, if Oracle has it why not SQL Server. A sequence object generates sequence of unique numeric values as per specifications. Many developers would have now got a thought, we have something similar like this called as “Identity” columns. But the big difference is sequence object is independent of a table while identity columns are attached to a table.

Below is a simple code to create a sequence object. You can see we have created a sequence object called as “MySeq” with the following specification:-

  • Starts with value 1.
  • Increments with value 1 Minimum value it should start is with zero.
  • Maximum it will go to 100. No cycle defines that once it reaches 100 it will throw an error.
  • If you want to restart it from 0 you should provide “cycle”.
  • “cache 50”  specifies that till 50 the values are already incremented in to cache to reduce IO. If you specify “no cache” it will make input output on the disk.
create sequence MySeq as int
	start with 1  -- Start with value 1
	increment by 1-- Increment with value 1
	minvalue 0 -- Minimum value to start is zero
	maxvalue 100 -- Maximum it can go to 100
	no cycle -- Do not go above 100
	cache 50 -- Increment 50 values in memory rather than incrementing from 
IO

To increment the value we need to call the below select statement. This is one more big difference as compared to identity.In identity the values increment when rows are added here we need to make an explicit call.

SELECT NEXT VALUE FOR dbo.MySequence AS seq_no;

Feature number 3 (Revolution):- Pagination

There are instances when you want to display large result sets to the end user. The best way to display large result set is to split them i.e.  apply pagination. So developers had their own hacky ways of achieving pagination using “top”, “row_number” etc. But from SQL Server 2012 onwards we can do pagination by using “OFFSET” and “FETCH’ commands.

For instance let’s says we have the following customer table which has 12 records. We would like to split the records in to 6 and 6. 

Image 5

So doing pagination is a two-step process: -

  • First mark the start of the row by using “OFFSET” command.
  • Second specify how many rows you want to fetch by using “FETCH” command.

You can see in the below code snippet we have used “OFFSET” to mark the start of row from “0”position. A very important note order by clause is compulsory for “OFFSET” command.

select * from
tblcustomer order by customercode
offset 0 rows – start from zero

In the below code snippet we have specified we want to fetch “6” rows from the start “0”position specified in the “OFFSET”.

fetch next 6 rows only

Now if you run the above SQL you should see 6 rows.

Image 6

To fetch the next 6 rows just change your “OFFSET” position. You can see in the below code snippet I have modified the offset to 6. That means the row start position will from “6”.

select * from
tblcustomer order by customercode
offset 6 rows



fetch next 6 rows only

The above code snippet displays the next “6” records , below is how the output looks.

Image 7

Feature number 4 (Revolution):- Contained database

This is a great feature for people who have to go through pain of SQL Server database migration again and again. One of the biggest pains in migrating databases is user accounts.  SQL Server user resides either in windows ADS or at SQL Server level as SQL Server users.  So when we migrate SQL Server database from one server to other server these users have to be recreated again. If you have lot’s of users you would need one dedicated person sitting creating one’s for you.

So one  of the requirements from easy migration perspective is  to create databases which are self-contained. In other words, can we have a database with meta-data information, security information etc with in the database itself. So that when we migrate the database, we migrate everything with it.  There’s where “Contained” database where introduced in SQL Server 2012.

Creating contained database is a 3 step process: -

Step 1: - First thing is to enable contained database at SQL Server instance level. You can do the same by right clicking on the SQL Server instance and setting  “Enabled Contained Database” to “true”.

Image 8

You can achieve the same by using the below SQL statements as well.

sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Step 2 - The next step is to enable contained database at database level. So when create a new database set “Containment type” to partial as shown in the below figure.

Image 9

You can also create database with “containment” set to “partial” using the below SQL code.

CREATE DATABASE [MyDb]
CONTAINMENT = PARTIAL
ON PRIMARY
( NAME = N'My', FILENAME = N'C:\My.mdf')
LOG ON
( NAME = N'My_log', FILENAME =N'C:\My_log.ldf')

Step 3: - The final thing now is to test if “contained” database fundamental is working or not. Now we want the user credentials to be part of the database , so we need to create user as “SQL User with password”.

Image 10

You can achieve the same by using the below script.

CREATE USER MyUser
WITH PASSWORD = 'pass@123';
GO

Now if you try to login with the user created, you get an error as shown in the below figure. This proves that the user is not available at SQL Server level.

Image 11

Now click on options and specify the database name in “connect to database” , you should be able to login , which proves that user is part of database and not SQL Server

Image 12

Feature number 5 (Evolution):- Error handling

As a developer I am personally very comfortable with using “try/catch/throw” syntax structure for error handling in c# or vb.net. Thanks to SQL Server team in 2005 they brought in “try/catch” structure which is very much compatible the way I as a developer was doing error handling in c#.  It was nightmare handling error using “IF” conditions and “@error” code before SQL Server 2005. Below is a sample code which shows how “try/catch” code looks.

begin try


declare @n int = 0;
set @n = 1/0;


end try


begin catch


print('divide by zero');
RAISERROR ( ‘Divide by zero‘, 16, 1) ;


end catch

But what still is itching me in the above code is when it comes to propagating errors back to the client I was missing the “THROW” command.  We still need to  use “RAISEERROR” which does the job, but lacks lot of capabilities which “THROW” has. For example to throw user defined messages you need to make entry in to “sys.messages” table.

Below is how the code with “throw” looks like.

begin try
-- The code where error has occurred.
end try


begin catch
-- throw error to the client
Throw;
end catch

If you want to throw exception with a user defined message defined you can use the below code. No entry need in the “sys.messages” table.

THROW 49903, 'User define exception.', 1

From SQL Server 2012 onwards use “Throw” rather than “raiseerror” , looking at the features of “throw” looks like sooner or later “raiseerror” will be deprecated . Below is a comparison table which explains the difference between “throw” vs “raiseerror”.

  Throw RaiseError
User & system exception Can generate only user exception. Can generate user and system exception.
“Sys.Messages” table You can supply adhoc text does not need an entry in “Sys.Messages” table. You need to make an entry in “Sys.Messages” table.
Original exception. Original exception is propagated to the client. Original exception is lost to the client.

Which are the next 5 features?

In the next part we will talk about the below features :- http://www.codeproject.com/Articles/561797/Top-20-exciting-features-of-SQL-Server-2012-Part-2  

Feature number 6 (Evolution):- User defined roles

Feature number 7 (Evolution):- Windows server core support

Feature number 8 (Revolution):- Tabular Model (SSAS)

Feature number 9 (Revolution):- Power view

Feature number 10 (Revolution):- DQS Data quality services 

If you ever get a chance do visit my site for  .NET and SQL Server interview questions and answer videos.  

For further reading do watch the below interview preparation videos and step by step video series.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect https://www.questpond.com
India India

Comments and Discussions

 
PraiseGood article Pin
Varun Sareen27-Jan-17 0:09
Varun Sareen27-Jan-17 0:09 
QuestionVery Good Article.. Pin
K V Maniraj1-Nov-14 18:14
K V Maniraj1-Nov-14 18:14 
GeneralVery nice article Pin
subbu_msis20-Aug-14 1:11
subbu_msis20-Aug-14 1:11 
QuestionThank you Pin
machag6-Aug-14 6:26
machag6-Aug-14 6:26 
QuestionLooking out for next 10 features Pin
Roshan Zanwar14-Jul-14 23:51
Roshan Zanwar14-Jul-14 23:51 
GeneralExcellent Fast Track Overview Pin
The Data Man9-Jun-14 6:14
The Data Man9-Jun-14 6:14 
GeneralMy vote of 1 Pin
Piyush K Patel16-Apr-14 20:06
professionalPiyush K Patel16-Apr-14 20:06 
GeneralMy vote of 5 Pin
Sanjay K. Gupta27-Jan-14 2:38
professionalSanjay K. Gupta27-Jan-14 2:38 
GeneralThanking Pin
Srikar Reddy19-Jan-14 23:11
Srikar Reddy19-Jan-14 23:11 
GeneralMy vote of 5 Pin
Mukul0039-May-13 1:17
professionalMukul0039-May-13 1:17 
GeneralMy vote of 4 Pin
Anupama Agarwal14-Apr-13 22:09
Anupama Agarwal14-Apr-13 22:09 
Well written
GeneralMy vote of 5 Pin
abiratsis4-Apr-13 5:28
abiratsis4-Apr-13 5:28 
GeneralMy vote of 5 Pin
Prasad Khandekar14-Mar-13 4:52
professionalPrasad Khandekar14-Mar-13 4:52 
GeneralRe: My vote of 5 Pin
Shivprasad koirala14-Mar-13 5:11
Shivprasad koirala14-Mar-13 5:11 
GeneralMy vote of 5 Pin
Rekhash8-Mar-13 0:13
Rekhash8-Mar-13 0:13 
GeneralRe: My vote of 5 Pin
Shivprasad koirala14-Mar-13 5:14
Shivprasad koirala14-Mar-13 5:14 
GeneralMy vote of 5 Pin
Monjurul Habib15-Feb-13 11:28
professionalMonjurul Habib15-Feb-13 11:28 
GeneralMy vote of 5 Pin
Abinash Bishoyi11-Feb-13 5:20
Abinash Bishoyi11-Feb-13 5:20 
GeneralRe: My vote of 5 Pin
Shivprasad koirala14-Mar-13 6:01
Shivprasad koirala14-Mar-13 6:01 
GeneralMy vote of 5 Pin
member6023-Jan-13 17:42
member6023-Jan-13 17:42 
GeneralMy vote of 5 Pin
Naeem Mehmood20-Jan-13 23:32
Naeem Mehmood20-Jan-13 23:32 
GeneralRe: My vote of 5 Pin
Shivprasad koirala14-Mar-13 6:02
Shivprasad koirala14-Mar-13 6:02 
GeneralMy vote of 5 Pin
WebMaster20-Jan-13 15:11
WebMaster20-Jan-13 15:11 
QuestionMy vote Pin
yoyogesh18-Jan-13 2:05
yoyogesh18-Jan-13 2:05 
GeneralMy vote of 5 Pin
magicpapacy17-Jan-13 14:45
magicpapacy17-Jan-13 14:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.