BLOGS

ByHariharan Rajendran

Temporary Tables & Table Variable

In SQL Server, temp tables are created at run time. It is just like a normal table and it will support all the operations but these tables are created inside temp database. The scope of the table is only on session & connection.

There are two types of temporary tables,

  1. Local Temp Table
  2. Global Temp Table

Local Temp Table

It will be available only on SQL Server session or connection. It means, if the session is closed or connection is out then this temp table will not be accessible, it will be deleted.

This will be accessible only one user who creates the table.

Local Temp table name is started with “#” sign.

Syntax

CREATE TABLE #LocalTemp

(

 Columns

)

Example

–Create a local Temp Table

CREATE TABLE #LocalTemp

(

 UserID int,

 Name varchar(50),

 EmailId varchar(50),

 Phonenumber varchar(15)

)

GO

–Insert values directly

Insert into #LocalTemp values (1,‘Hari’‘Test@Test.com’‘123-245-356’)

Go

–Insert value from another table

Insert into #LocalTemp 
(UserIDName, EmailIdPhonenumber)

(SELECT UserId, NameEmailId, Phonenumber from dbo.USERS)

Go

Try to open a new query window and access the above table then see the result by yourself.

TempTable

Global Temp Table

This table will be available for all SQL Server sessions. This can be accessible by multiple users with the same connections. It will be deleted when a connection is closed or all the sessions are closed.

Global Temp table name is started with “##” sign

Syntax

CREATE TABLE ##GlobalTemp

(

 Columns

)

GO

Example

–Create a local Temp Table

CREATE TABLE ##GlobalTemp

(

 UserID int,

 Name varchar(50),

 EmailId varchar(50),

 Phonenumber varchar(15)

)

GO

–Insert values directly

Insert into ##GlobalTemp values (1,‘Hari’‘Test@Test.com’‘123-245-356’)

Go

–Insert value from another table

Insert into ##GlobalTemp 
(UserIDName, EmailIdPhonenumber)

(SELECT UserId, NameEmailId, Phonenumber from dbo.USERS)

Go

Open multiple query window and access this table then close the connection and connect again. Then try to access the table by yourself.

Table Variable

Apart from temp tables, we have table variable and the scope of this table variable only with the batch of script.

It will be dropped once it comes out of the batch.

This is also created on temp database but not on memory. This allows us to create primary key on table as like normal table when declare a table variable but the difference is we cannot create cluster index.

Example

–First Batch Start

–Create a table variable

DECLARE @Users TABLE

 (

 UserID int
identity (1,1),

 Name varchar(50),

 EmailId varchar(50),

 Phonenumber varchar(15)

 )

  –insert values from another table

 Insert into @Users
(Name, EmailIdPhonenumber)

 SELECT  NameEmailId, Phonenumber from dbo.USERS

 –Select the records

 SELECT * FROM @Users

 –First Batch End …………..

 –Second batch start

 SELECT * FROM @Users –gives error

 Go

 –Second Batch End….

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.

Install_RStudio_099_01

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.

Install_RStudio_099_02

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

Install_RStudio_099_03

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

Install_RStudio_099_04

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

Install_RStudio_099_05

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

Encryption

 

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

 PRIMARY KEY CLUSTERED ([UserID] ASC) ON [PRIMARY] )

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.

Import1

import2

import3

import4

import5

import6

import7

import8

 

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>

 ENCRYPTED WITH (ENCRYPTION_TYPE = <Type of Encryption>,

 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)

COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,

ALGORITHM ‘AEAD_AES_256_CBC_HMAC_SHA_256’, COLUMN_ENCRYPTION_KEY = MyColumnKeyNOT NULL,

[FirstName] nvarchar(50) NOT NULL,

[LastName] nvarchar(50NULL,

[Designation] nvarchar(50NULL,

[Email] nvarchar(50NOT NULL,

[Phone Number] nvarchar(10NOT NULL,

[Salary] decimal(10,2

ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZEDALGORITHM ‘AEAD_AES_256_CBC_HMAC_SHA_256’,

COLUMN_ENCRYPTION_KEY = MyColumnKeyNOT NULL

PRIMARY KEY CLUSTERED ([UserID] ASCON [PRIMARY] )

 

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,

EncryptionKey2

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:

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))

 

composite key