Author Archives: Hariharan 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.

 

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:

CREATE TABLE SAMPLE_TABLE
(COL1 int,
COL2 varchar(30),
COL3 varchar(50),
PRIMARY KEY (COL1, COL2));

Example.

CREATE TABLE USERS

(UserId int,

Name varchar(50),

EmailId varchar(50),

Phonenumber varchar(50)

PRIMARY KEY (EmailId, Phonenumber))

 

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.

 

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.

CREATE TABLE Users

(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,

CREATE TABLE Users

(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.

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.

CREATE TABLE Users

(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,

CREATE TABLE Users

(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,

CREATE TABLE Users

( Id uniqueidentifier primary key,

Name varchar(50))

Steps to Create HDInsight Cluster in Azure

Step 1: Log into azure portal

Step 2: Click “Browse” and look for HDInsight Clusters which will have Hadoop icon.

Step 3: Click the HDInsight Clusters and “Add” a new cluster.

Step 4: Provide the cluster name and other details as like below and cluster type should be “Hadoop”.

Step 5: Cluster credentials are mandatory so define the credentials. In this section, you can enable remote desktop by selecting “YES”, need to provide remote desktop credentials.

Step 6: Datasource must be configured for cluster.

Step 7: Node pricing tiers is required to configure as like below.

Step 8: Click the create button when all the steps are filled and configured. HDInsight cluster will be created and deployed for your use.

 

 

Dataset for Power BI from SharePoint Team Site

We discussed about preparing datasets using files from different environment (Local drive & OneDrive) in my previous articles.  Go through here.

We have another option under files section which is “SharePoint – Team Sites”.

It is very easy to get a file (dataset) from SharePoint as we are using business email to connect Power BI so no need to provide the credentials again.

Select the file from SharePoint and connect to Power BI. File will be added in to Datasets.

 

Dataset for Power BI from Onedrive

In my last article, we discussed about how to prepare a dataset from local file.

This article speaks about how we can create a dataset which is stored in OneDrive. It can be personal or business OneDrive.

There is no changes up to 4th step from my previous article, after that follow the below steps

1. Choose either OneDrive – Business or OneDrive – Personal where the file stored. 
2. Here am using OneDrive – Personal, click it.
3. It will ask for Microsoft account credentials to access OneDrive.
4. Choose the file from OneDrive and select Connect which is on top right.
5. Selected file will be available under Datasets in Power BI