BLOGS

ByHariharan Rajendran

Temporal Table in SQL Server 2016

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,

  1. In real time, data is not going to be static. To track the changes (Auditing) of the data over a point of time.
  2. Recovering from accidental data changes.

Prerequisites to create temporal table,

  1. The table should have a primary key column
  2. Two columns should have datatime2 data type to track the data validity (from & to).

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.

ByManjunath Jogalekar

Alert Feature in Microsoft SQL Server

Alert feature in Microsoft SQL server is one of the prominent feature available in SQL server. Before we understand what Alert feature is and how to make use of the feature, first let us understand about basic meaning of Alert.

Alert is a warning or notification highlighted or notified to individual, group or other subsequent action for the predefined scenarios. Having alert system will keep away any of the future hazardous or hefty loss.

In SQL server Alert work similar as the Alert system available in any of the other systems available in other systems or process. To feel the alert system how it works you can consider the smartphones or any other mobile devices which through warning when there Is a low battery or any communication received.

As simple example earlier, SQL server alert system behaves the same way where it notifies the end user about any performance issues, Starting or stopping the process, intimating the end user through mail or other any communication methods.

SQL server Alert system is managed through SQL Server agent and SQL server agent should be up and running to make use of SQL server Alert System feature.

SQL Server Alert system can notify the end users through following communication methods.

  • Email
  • Net share
  • Pager
  • In SQL server 2014 there are three type Alert system
  • SQL Server Event Alert
  • SQL server Performance Condition Alert
  • WMI Event alert
  • The following steps will instruct to set up Alert system in SQL server
  • Expand SQL server agent and make sure SQL server agent is up and running.

01

  • Right click on the alert system and click new Alert

02

  • Post to the previous step alert configuration window will be open. In the configuration window update the properties for the alert. In our example we have considered Alert system for SQL server performance condition alert type and the properties provided for Alert system will change according to the type of alert selected.

03

  • Name: Provide the alert Name which is going to be the identification for the Alert.
  • Type: Select the type of Alert (In this case we have selected SQL server performance condition alert.)
  • Counter: Configure the counter to choose the required counter ( In our Case it SQL server databases)
  • Instance : Configure the Instance for which Notification has to be processed ( Note: This is a counter instance and not server instance)
  • Alert: Here we will configure when the alert should be configured. In this case alert will be triggered if ‘Dageop’ log file exceed 75%
  • Move to the response tab provided in the left pane of configuration window. In the response window configure the Alert either to send the mail or to trigger some process or it might be both. In the below case we have considered both to send mail and also to trigger the ‘Flush_Dageop_Log’

04

  • The above steps will create Alert called ‘Log_Exceed_Alert’ which can be later redefined by double clicking the Alert from Alert folder in SQL server agent. This Alert will trigger Flush_Dageop_Log package to reduce the growth of Dageop database log file and also notification will be provided to the end user.

05

 

ByHariharan Rajendran

Vertipaq

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.

  1. Value Encoding
  2. Dictionary Encoding
  3. Run Length Encoding (RLE)

Check this link, for detailed information about VertiPaq Compression techniques.

ByHariharan Rajendran

Data Storage Format in Columnstore Index

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,

ID Name Department
1 xyz Production
2 Robert Information Technology
3 Mary Sales
4 John Account

 

Row store
1 xyz Production
2 Robert Information Technology
3 Mary Sales
4 John Account

 

Column Store
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.

 

ByHariharan Rajendran

Basic Concepts of Power BI

Check my previous post about introduction to Power BI before going through this article.

Creating a report and dashboards are very easy in Power BI by simply pointing to the correct data source.

Levels in Power BI,

1. Create a dataset
2. Create a report on top of above dataset
3. Create a dashboard – Just pin a charts to display in dashboard from report.

Power BI is an application which we can use in online as well as in desktop. The steps provided in my previous article (Link) is for online version of Power BI.

To download the desktop version, click here. It has more options compare than online version.

Power BI has gateways which helps to keep the data fresh by connecting the on-premises data sources. It provides the flexibility to individuals as well as organization.

ByDr. SubraMANI Paramasivam

7 key habits, practice & experience, which made Bill Gates succeed

I just flipped through my phone and found this amazing article which might be a great energy re-charge for many youngsters today. This clearly talked about the key habits, practise and experience from Bill Gates that helped him succeed. This is just a simple formulae for all of us to do, what we do should have hard core dedication & hard work to succeed. The main 7 key points highlighted are

  1. Get in early – and learn
  2. Seek forgiveness, not permission
  3. Value your work
  4. Learning matters more than school
  5. When you’re in charge, take charge
  6. Be the guy who predicts the future
  7. Take on a big enough mission.

What a great re-invention that we should really apply in our day to day life. I sincerely advise and suggest to all of you who don’t do this or have done this in the past but don’t do it anymore, to restart in doing the same.

All the very best.

 

ByHariharan Rajendran

Introduction to Power BI

Power BI is one of the best visualization tool and it can be used by anyone. Initially this tool was available for office 365 SharePoint site. It was something like an add-on but now they have upgraded and it is available externally.

Power BI team from Microsoft has put their ideas and innovations to make this tool available to everyone and it is not  required much technical knowledge to use.

There are lots of improvement has been taking place from day one of power BI by Microsoft power BI team.

Steps to Use Power BI

  1. Visit https://powerbi.microsoft.com/
  2. Scroll down and look for option “Use it free”
  3. Enter work email (xxxxx@xxxx.com), it won’t support personal email
  4. Click “Use it free” and email will be received from power BI and follow the instructions.
ByHariharan Rajendran

Purpose of Columnstore Index in SQL Server 2012

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.

  1. The Volume of data is high, so the ability of data warehouses to execute the queries within a time is not achievable.
  2. More time is required to Tune the queries.
  3. Designing and maintaining multiple indexes.

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.