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