SQL Server 2012 New Features

SQL Server 2012 was released on April 2012 and here is list of new features

1. Column Store Indexes
2. Sequence Objects
3. Pagination
4. Contained Database
5. Error Handling
6. User Defined Roles
7. Windows Server Core Support
8. SSAS Tabular Models
9. Power View
10. Data Quality Services
11. Always on
12. Trouble shooting in SSIS
13.  SSIS CDC support 
14. SSIS parameter support.

Column Store Indexes
The columnstore index is the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage, and up to 10x data compression over the uncompressed data size. 

Advantages

1. Columns store values from the same domain and commonly have similar values, which results in high compression rates. This minimizes or eliminates IO bottleneck in your system while reducing the memory footprint significantly.
2. High compression rates improve query performance by using a smaller in-memory footprint. In turn, query performance can improve because SQL Server can perform more query and data operations in-memory.
3. Batch execution improves query performance, typically 2-4x, by processing multiple rows together.
4. Queries often select only a few columns from a table, which reduces total I/O from the physical media.

Use Cases

1. Use a clustered columnstore index to store fact tables and large dimension tables for data warehousing workloads. This improves query performance and data compression by up to 10x.
2. Use a nonclustered columnstore index to perform analysis in real-time on an OLTP workload. 

column store
A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

row store
A rowstore is data that is logically organized as a table with rows and columns, and then physically stored in a row-wise data format. This has been the traditional way to store relational table data. In SQL Server, rowstore is the underlying data storage format for a heap, a clustered index, and an in-memory table.

row group
A row group is a group of rows that are compressed into columnstore format at the same time. A rowgroup usually contains the maximum number of rows per rowgroup which is 1,048,576 rows.
For high performance and high compression rates, the columnstore index slices the table into groups of rows, called rowgroups, and then compresses each rowgroup in a column-wise manner. The number of rows in the rowgroup must be large enough to improve compression rates, and small enough to benefit from in-memory operations.

column segment
A column segment is a column of data from within the rowgroup.
Each rowgroup contains one column segment for every column in the table.
Each column segment is compressed together and stored on physical media.

Note:
All of the columns in a columnstore index are stored in the metadata as included columns. The columnstore index does not have key columns.
SELECT * FROM sys.indexes

SELECT * FROM sys.index_Columns

Example:

Step 1: Create a copy of existing fact table
SELECT ProductKey, UnitPrice, CustomerPONumber, OrderDate into FactResellerSales_Test
FROM FactResellerSales;

Step 2: Create Non Clustered Column Store Index on newly created fact table
CREATE NONCLUSTERED COLUMNSTORE INDEX csi_FactResellerSales_Test
ON dbo.FactResellerSales_Test
(ProductKey, UnitPrice, CustomerPONumber, OrderDate);

Step 3: Fire a select against both tables and compare execution plans

SELECT ProductKey, UnitPrice, CustomerPONumber, OrderDate 
FROM FactResellerSales;

SELECT ProductKey, UnitPrice, CustomerPONumber, OrderDate

FROM FactResellerSales_Test;

         

Sequence Objects

A sequence object generates sequence of unique numeric values as per specifications. 

A sequence can be defined as any integer type. The following types are allowed. If no data type is provided, the bigint data type is used as the default.

1. tinyint - Range 0 to 255
2. smallint - Range -32,768 to 32,767
3. int - Range -2,147,483,648 to 2,147,483,647
4. bigint - Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
5. decimal and numeric with a scale of 0.
6. Any user-defined data type (alias type) that is based on one of the allowed types.

Syntax

CREATE SEQUENCE [schema_name . ] sequence_name
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]
    [ START WITH <constant> ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]

Example

We will create sequence object called as “OrderNumberSeq” with the following specification:-

1. Starts with value 1000.
2. Increments with value 1 Minimum value it should start is with zero.
3. Maximum it will go to 10000. 
4. No cycle defines that once it reaches 10000 it will throw an error.
5. Note: If you want to restart it from 0 you should provide “cycle”.
6. “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 OrderNumberSeq as int
start with 1000  
increment by 1
minvalue 0 
maxvalue 10000 
no cycle 

cache 50 

To increment the value we need to call the below select statement. 

SELECT NEXT VALUE FOR dbo.OrderNumberSeq AS seq_no;

Execute the following statement to view the properties of the sequence and to get current value of sequence.

SELECT * FROM sys.sequences WHERE name = 'OrderNumberSeq' ;

SELECT current_value, cache_size, CURRENT_USER FROM sys.sequences WHERE name = 'OrderNumberSeq' ;

sp_sequence_get_range

Returns a range of sequence values from a sequence object.

DECLARE  
  @FirstSeqNum sql_variant
, @LastSeqNum sql_variant
, @CycleCount int
, @SeqIncr sql_variant
, @SeqMinVal sql_variant
, @SeqMaxVal sql_variant ;

EXEC sys.sp_sequence_get_range
@sequence_name = N'dbo.OrderNumberSeq'
, @range_size = 5
, @range_first_value = @FirstSeqNum OUTPUT 
, @range_last_value = @LastSeqNum OUTPUT 
, @range_cycle_count = @CycleCount OUTPUT
, @sequence_increment = @SeqIncr OUTPUT
, @sequence_min_value = @SeqMinVal OUTPUT
, @sequence_max_value = @SeqMaxVal OUTPUT ;

-- The following statement returns the output values
SELECT
  @FirstSeqNum AS FirstVal
, @LastSeqNum AS LastVal
, @CycleCount AS CycleCount
, @SeqIncr AS SeqIncrement
, @SeqMinVal AS MinSeq

, @SeqMaxVal AS MaxSeq ;

Dropping Sequence
drop sequence OrderNumberSeq


Pagination

The OFFSET-FETCH clause provides you with an option to fetch a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.

[ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n]

{OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }    
[FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]}

Full Resultset
SELECT [object_id], NAME  FROM SYS.TABLES ORDER BY [object_id] 

Example 1 Skip first 2 rows from the sorted result set and return the remaining rows.

SELECT [object_id], NAME  FROM SYS.TABLES ORDER BY [object_id]
OFFSET 2 ROWS

Example 2 Skip first 15 rows from the sorted result set and return next 5 rows.


SELECT [object_id], NAME  FROM SYS.TABLES ORDER BY [object_id]
OFFSET 2 ROWS

FETCH NEXT 1 ROWS ONLY;

Contained Database

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2016 helps user to isolate their database from the instance in 4 ways.

• Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the masterdatabase.)
• All metadata are defined using the same collation.
• User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
• The SQL Server environment (DMV's, XEvents, etc.) reports and can act upon containment information.

Example:

sp_configure 'show advanced options',1

GO

RECONFIGURE WITH OVERRIDE

GO

sp_configure 'contained database authentication', 1

GO

RECONFIGURE WITH OVERRIDE

GO

CREATE DATABASE [MyTestDb]

CONTAINMENT = PARTIAL

ON PRIMARY

( NAME = N'MyTestDB', FILENAME = N'E:\MyTestDB.mdf')

LOG ON

( NAME = N'MyTestDB_log', FILENAME =N'E:\MyTestDB_log.ldf')

CREATE USER MyTestUser

WITH PASSWORD = 'test123456';

GO

Now, For Connecting to new contained database you have to specify database name in options tab.

Error Handling

SQL Server 2012 has introduced Throw Statement for showing user defined Error Message.

SyntaxTHROW [ { error_number | @local_variable },
        { message | @local_variable },
        { state | @local_variable } ]
[ ; ]

The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

Example 1

CREATE PROCEDURE dbo.spTestThrow

AS

BEGIN 

BEGIN TRY  

select 1/0 -- Divide By Zero 

END TRY 

BEGIN CATCH       

  THROW 500001, '"Divide By Zero" - Error Message From New, Throw Statement                 Introduced in SQL 2012', 1 

END CATCH

END

EXEC dbo.spTestThrow






Example 2

DECLARE @Num1 INT = 100 

DECLARE @Num2 INT = 0  



IF(@Num2 <> 0) 

BEGIN     



SELECT @Num1/@Num2 END ELSE BEGIN      THROW 500001, '"Divide By Zero" - Error Message From New, Throw Statement                Introduced in SQL 2012', 1 



END

User Defined Roles

In SQL Server 2012 you can create your own role and define customized permission for the role at a more granular level. 

Windows Server Core Support

1. Windows server core is one of the flavors of Windows operating system. 
2. It is a GUI less version of windows operating system. When you boot with windows core you would be surprised to get a simple DOS command line 
3. Because only necessary services are enabled, we have less memory consumption, simplified management as many features are not enabled and great stability. 
4. So this is the most welcome feature and on production server using windows core is definitely the way to go.

T-SQL Enhancements 

1. TRY_CONVERT() allows you to ignore invalid conversions and return NULL for those values instead of returning an error for the entire query. 
2. OFFSET / FETCH 
3. FORMAT()