Microsoft SQL Server 2016 has one of the exciting feature called PolyBase. It is acts as a bridge between relational database and Hadoop.
We can access all structured, unstructured and semi-structured data using known language of SQL.
Polybase allows user to use T-SQL Statements in Microsoft SQL Server Management studio to access data stored in Hadoop or Microsoft Azure blob storage.
Below services should up and running to use this feature.
To check the installed services in system,
Go through my previous article here, about Data storage format in columnstore Index.
There are lots of improvements in Query processing speed when we use columnstore Index.
Temporal is a database feature that was introduced in ANSI SQL 2011 and this is now supported in SQL Server 2016.
It is a new type of user defined table and designed to keep track of all historical changes on the table and make easy to do point in time analysis.
Main reasons to use temporal table,
Prerequisites to create temporal table,
Syntax to create Temporal Table
CREATE TABLE <Table Name>
<column 1> int NOT NULL PRIMARY KEY CLUSTERED
,<column 2> <datatype>
, <column n>
, [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
, [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = <schema>.<tablename> –Optional));
We can give any name instead of ValidFrom and ValidTo, both should be bind in PERIOD.
Vertipaq is an engine which was found in Power Pivot for Analysis server in SQL Server 2008 R2. Basically, it is used for data compression. Data retrieval and calculations are happening at much faster rate as it holds the compressed data in memory. It is also called xVelocity and it has been added to SQL Server 2012. As a result, it is delivering very huge performance improvements for data warehouse and business intelligence solutions.
Columnstore index using this compression algorithm technology.
This algorithm using following compression techniques.
Check this link, for detailed information about VertiPaq Compression techniques.
Go through my previous article purpose of columnstore index.
Normally SQL Server stores the data in pages in a row based manner for tables (heaps) and B-tree indexes. This is the traditional approach and technically this is called “Row store”.
Column store is something like turning traditional storage model into 90 degrees. In this approach, all the values in single column will be in compressed form. Column store index stores each column in a separate set of disk pages which is different from the traditional storage format.
Let’s see an example,
|1 xyz Production|
|2 Robert Information Technology|
|3 Mary Sales|
|4 John Account|
|1 2 3 4|
|Xyz Robert Mary John|
|Production Information Technology Sales Account|
Column store index is more compressed than the row store. This can understand by seeing above examples. In row store format we have different data types involved in a single row (ID is Integer, Name is string, Department is string). Row store method trying to compress the different data type fields, but in column store, all the values in a same data type so it is easy to compress the data.
Data Compression percentage of column store is greater than the row store.
Nowadays, data being generated and captured across many devices, applications and services. Now every organization is tasked with storing massive amounts of data to accelerate their business growth success.
Using traditional tools to capture and process the data is very challenging and it is not going to be one time activity because all data users want to see continuous growth.
We have below issues while doing this task.
In many cases, so much of time elapsed between the time the query start executed and time to render the result.
To overcome the above issues, Microsoft SQL Server team worked on columnstore Index, which allow very large data set to be read quickly.
The performance of the columnstore index is far better than normal index. This has been introduced in Microsoft SQL Server 2012.
Ease of access and easy life is now possible with On-premises and hybrid cloud technology with SQL Server 2016.