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,
Encryption Types.
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))
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
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
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
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,
Primary Key on unique identifier field,
Points to remember,
Primary key on varchar field, consider an emailid field
Point to remember,
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,
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))
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.
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.
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