ByDr. SubraMANI Paramasivam

Install R Studio 0.99.491

You can download the latest R Studio 0.99.491 and install for FREE in this link. However if you need support then you need to agree to a monthly term to support you and your production system, and you can compare it from this link about the pricing for your desktop and server.

I have downloaded the RStdio-0.99.491 and have started installing and provided you the below screenshots for your reference.


In here, you have option to choose your folder and it also gives you the amount of space required for this installation. I know, I am running out of space, but I am doing this in my virtual machine.


Here you get option to add to the start menu and create shortcuts. I chose the default options in below screenshot.


As you can see below, the extraction and installation takes place for about couple of minutes.


That’s it. We are now done with the installation of R Studio.


ByHariharan Rajendran

Test Drive on Always Encrypted in SQL Server 2016

To test the always encrypted feature in SQL Server 2016, Follow the below test steps,

  1. Create a table with encrypted column, refer here to set up.
  2. Insert a data into the table
    1. Use client application to insert a data, refer here.
    2. Export and import from another table, refer here.
  3. Check the table result

Tables used in my test,

  1. Employees – Always Encrypted on TaxID & Salary fields
  2. Employees_NoEncrypt



ByHariharan Rajendran

Insert a data into Encrypted Table (Always Encrypted)

Following my previous post, you can insert a data into an encrypted table from another table using Export and Import option.

Create a table with same structure without encrypt settings- Compare the table (Employees) structure of encrypted table here

CREATE TABLE [dbo].[Employees_NoEncrypt]

( [UserID] int IDENTITY(1,1),

[TaxID] varchar(11) NOT NULL,

[FirstName] nvarchar(50)NOT NULL,

[LastName] nvarchar(50) NULL,

[Designation] nvarchar(50) NULL,

[Email] nvarchar(50) NOT NULL,

[Phone Number] nvarchar(10) NOT NULL,

[Salary] decimal(10,2)  NOT NULL


Insert a data into Employees_NoEncrypt table using “Insert” statement or “Edit Top 200 Rows”. Once done then go to next step.

Use Import and Export Data wizard – This will be installed when you install SQL Server.

In my case, both the tables are residing in same database and server.










ByHariharan Rajendran

Column Level Encryption using Always Encrypted in SQL Server 2016

Always encrypted feature in SQL Server 2016 is the best option who wants to encrypt the certain column values in a table.

Steps to implement the always encrypted option,

  1. Set up column encryption key with the help of column master key – Go through column master and encryption article here to set up.
  2. Create a table with column level encryption setting
    1. Column encryption key
    2. Algorithm – Currently only one algorithm is supported which is AEAD_AES_256_CBC_HMAC_SHA_256
    3. Type of encryption – DETERMINISTIC or RANDOMIZED. Visit here to know detailed information

Syntax to apply encryption on a column,

CREATE TABLE <tablename>

 (<column name<data type>


 ALGORITHM AEAD_AES_256_CBC_HMAC_SHA_256COLUMN_ENCRYPTION_KEY =<column encryption key name>)


In this below table, I want to encrypt employee Tax ID and Salary fields,

CREATE TABLE [dbo].[Employees]

( [UserID] int IDENTITY(1,1),

[TaxID] varchar(11)



[FirstName] nvarchar(50) NOT NULL,

[LastName] nvarchar(50NULL,

[Designation] nvarchar(50NULL,

[Email] nvarchar(50NOT NULL,

[Phone Number] nvarchar(10NOT NULL,

[Salary] decimal(10,2





By using the above script, we can create a table with encrypted columns.

If you try to insert data into the table using insert statement then you will get an error message like below,

Msg 206, Level 16, State 2, Line 16

Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = ‘DETERMINISTIC’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘MyColumnKey’, column_encryption_key_database_name = ‘Databrain’) collation_name = ‘SQL_Latin1_General_CP1_CI_AS’

It will not allow you to insert a data directly because of applied encryption. This can be done through a client application. Refer Microsoft article here for the detailed explanation.


ByHariharan Rajendran

[Step by Step] Column Master & Encryption Keys in SQL Server 2016

Following my previous article, this article will help you to understand how to create encryption keys which are the prerequisites for encrypting the table columns.

Steps to create Column Master Key

  1. Open SQL Server Management Studio
  2. Choose the database where you want to apply encryption for tables
  3. Expand the (+) sign
  4. Go to Security > Always Encrypted Keys > Column Master Key Definition
  5. Right click Column Master Key Definition folder and choose New Column Master Key Definition
  6. Provide the name
  7. Select the Key Definition Source from drop down (if no value then refresh)
  8. Select your machine
  9. Click Ok.

Screenshots for your reference,

EncryptionKey1    EncryptionKey3


Steps to create Column Encryption Key

  1. Open SQL Server Management Studio
  2. Choose the database where you want to apply encryption for tables
  3. Expand the (+) sign
  4. Go to Security > Always Encrypted Keys > Column Encryption Keys
  5. Right click Column Encryption Keys folder and choose New Column Encryption Key…
  6. Provide the name
  7. Choose the Column Master Key Definition
  8. Click Ok.

Screenshots for your reference,


ByHariharan Rajendran

Always Encrypted in SQL Server 2016

SQL Server 2016 introduces a new feature called “Always Encrypted”. It allows to encrypt the data in storage and also in motion (when reading a data). This helps us to secure the data.

There are several concepts involved in Always Encrypted,

  1. Column Master Key
    1. This is an encryption key that protects the column encryption keys.
    2. At least one master key should be available before encrypting any columns.
  2. Column Encryption Key
    1. This is the encryption key that actually protects the encrypted columns.
    2. This will make use of column master key.
  3. Column-Level Encryption setting
    1. Column must be set to encrypted using
      1. Column encryption key
      2. Algorithm
      3. Type of encryption

Encryption Types.

  1. Deterministic
    1. It is always encrypted to the same cyphertext (The result of encryption performed on plaintext using an algorithm).
    2. It can be used for operations like lookup (join), distinct, group by.
    3. It can be indexed.
  2. Randomized
    1. It is more secure.
    2. It cannot be used for operations like deterministic.
    3. Write and Read only possible
    4. It cannot be indexed.


ByHariharan Rajendran

Composite Key in SQL Server

This article is about to create primary key on multiple columns. Go through my previous article here to know about primary key and how to create it.

There are many scenarios where we need to create primary key on multiple columns.

For example, if you take E-learning sites. They are offering free trail and asking us to sign up using our email id and phone number to experience the free trail.

Once trail period is completed then we cannot apply for one more trail using same email id and phone number because the system will not allow inserting same combination again.

Primary key on multiple columns is called “Composite Key”.

Composite Key Syntax:

(COL1 int,
COL2 varchar(30),
COL3 varchar(50),



(UserId int,

Name varchar(50),

EmailId varchar(50),

Phonenumber varchar(50)

PRIMARY KEY (EmailId, Phonenumber))


composite key

ByHariharan Rajendran

Live Query Statistics

SQL Server 2016 introduces a new feature called “Live Query Statistics”.  This is the feature available in SQL Server Management studio and visible to everyone.

This live query plan displays below information when executing the query

  1. Overall query progress
  2. Number of rows produced
  3. Elapsed time on each operation

Data is available on run time so not required to wait for the query complete. This helps us to debug the query performance issues.

Steps to use this feature

  1. Check this feature in SSMS
  2. Include (select the icon) the “Live Query Statistics” before executing the script
  3. Execute the Script
  4. Check the Live Query Statistics window.