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.






ByDr. SubraMANI Paramasivam

Create a work station for a Data Scientist – Part 2

Following my first post on this same series on creating a work station for a Data Scientist, this will be the next one.

Next you need Revolution R Enterprise 7.5.0 Server (RRE-7.5.0) and you can download this from this link.

This will download about 81MB of “Revolution-R-Enterprise-Node-SQL-7.5.0-Windows.EXE”

Once downloaded you can then start installing this exe and below screenshots are provided for a successful installation.




















ByHariharan Rajendran

Database Table and Primary Key – Part 2

In my previous article, I have explained about the table creation and primary keys.

In real time scenarios, a primary key field should generate values automatically whenever a new row added to the table.

If the primary key field is an integer then set up an identity

  1. Identity needs to be defined in (X, Y) format
  2. Define the value for X which is starting value and Y is an increment value.


(Id int primary key identity (1,1),

Name varchar(50))

This table will generate a value for Id starting from 1 and increment by 1. The column values will be 1, 2, 3, .etc.

Primary key on unique identifier field,


(Id uniqueidentifier default NEWID() primary key,

Name varchar(50))

This will generate random new GUID value for each row.

Choosing a right primary key column is very challenging.

We can create a primary key on any of the columns but we need to understand the business requirement.

Consider, if you are creating a primary key on Integer field. It has both advantages and disadvantages. Most of the time people will consider its advantages and start using integer as primary key column for all the tables.

Points to remember,

  1. Performance wise, this is the best option
  2. Applying and rebuilding the index is very easy
  3. If we consider a big environment where data storage is important. Then int will hold values up to Inserting records more than this limit will break the table.

Primary Key on unique identifier field,

Points to remember,

  1. It is very good idea to use unique identifier as a primary key column when data is less in the table
  2. It will occupy more disk space
  3. If data is huge then indexing and rebuilding are very hectic.

Primary key on varchar field, consider an emailid field

Point to remember,

  1. Sometimes we might think that our table should maintain a unique email address so considering this we may create aprimary key on email field. This is not advisable.
  2. Email address is a field which will change over the period of time.
  3. If considering this as a primary key field and involved on many to many relationships then if any changes to the email address, it will affect the whole database.
ByHariharan Rajendran

Database Table and Primary Key – Part 1

Creating a table in MS SQL Server is very easy, this can be achieved by using T-SQL scripts or GUI. This article will use T-SQL Scripts to a create a table.

Basic Pre-Requisites to create a table,

  1. Understand the purpose of the table
  2. Identify the columns and data types. Refer this link, to know about data types.

Table is the storage unit in a database which stores the data in rows and columns as like excel sheet. The output of the table result is similar to the excel sheet data.

If you are decided to store some data in a database then you must use table.

The basic table structure will look like below.


(Id int)

We can add more columns in the structure, each column should be define with the right data type. Here Id is the column name and int is the data type which is nothing but an integer.

Let us discuss, what is a primary key and how to create it.

Primary key is a constraint which we need to define in any one of the column in a table. It will not allow null values in that defined column and entry to the column should be unique  i.e. it won’t allow duplicate values.

Choose an ID or Unique Identifier (GUID) column to create a primary key, this is the best way to define a table structure.

Create a table with primary Key on integer column,


(Id int primary key,

Name varchar(50))

In the above script, ID column is considered as a primary key. You can check this by clicking the columns folder under dbo.Users table.

Create a table with primary key on unique identifier column,


( Id uniqueidentifier primary key,

Name varchar(50))