MS SQL Server

ByDr. SubraMANI Paramasivam

Data Driven & SQL Server 2016

Following today’s Data Driven live streaming from New York, Satya Nadella have opened the speech by providing insights on what CEOs and organization owners should see with Data being the key factor driving the business.

DataDriven_02

DataDriven_01

 

Also SQL Server 2016 RC (Release Candidate) is now available here and no more CTPs from Microsoft download site. Be the first to download the latest Release Candidate and try the brand new features of SQL Server.

DataDriven_04

DataDriven_05

ByHariharan Rajendran

[Step by Step] Azure SQL Database Linked Server

As we are familiar with the known concept “Linked Server”. This article is about, how to create a linked server for Azure SQL database.

Make sure the firewall is enabled in Azure SQL Server.

Step 1: Go to SQL Server Management Studio

Step 2: Run the below scripts. This script using a stored procedure “SP_addlinkedserver”.

EXEC sp_addlinkedserver

@server=‘Azure’, –Provide Linked Server Name

@srvproduct=,    

@provider=‘sqlncli’,

@datasrc=‘tcp:<servername>.database.windows.net,1433’, –provide Azure SQl Server name

@location=,

@provstr=,

@catalog=‘SQLDB1’ –Provide azure database name

 

Step 3: Run the below script to provide the SQL Server login for the above created linked server.

exec sp_addlinkedsrvlogin ‘Azure’, ‘FALSE’, NULL, ‘<azure sql server login>’, ‘<sql server password>’;

 

Step 4: Verify the created linked server.

image

 

Step 5: Access the azure SQL database table. You need to use 4 part query. <linked server name>.<database name>.<schema name>.<table name>

 

image

ByHariharan Rajendran

In Memory Table in SQL Server

In a normal database, we can not create a in-memory tables. To create a in-memory table, we need to follow the below rules.

1. Create or Alter database to add a memory optimized data file group

2. Add a file into the file group.

Steps to alter your database to support in-memory tables. Before that let us try to create in-memory table in normal database.

image

Step 1: Alter the database to add memory optimized file group.

ALTER DATABASE [SampleDB] ADD FILEGROUP Analytics_Mode CONTAINS MEMORY_OPTIMIZED_DATA

 

Step 2: Add a file into the file group. Try to provide the path where SQL Server is installed.

ALTER DATABASE [SampleDB] ADD FILE (name=‘Analytics_Mode’,filename=‘E:\Local_Install_Applications\SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\InMemory1’) TO FILEGROUP Analytics_Mode

 

In the above script “InMemeory1” is a directory, don’t create this directory manually. The above script will create automatically and will populate the files.

Step 3: Now run the in-memory table script.

CREATE TABLE Account (

    ID int NOT NULL PRIMARY KEY NONCLUSTERED,

    AccountDescription nvarchar (50),

    AccountType nvarchar(50),

    Unit int

    )

    WITH (MEMORY_OPTIMIZED = ON );

GO

image

ByHariharan Rajendran

Insights on StretchDB SQL Server CTP 3.3

As we know that, the current version of stretch DB is migrating all the rows from local to Azure database table when there is no filter predicate is applied (in GUI, there is no option for filter predicate, it is possible via T-SQL script). At one point of time, there is no records in local table.

In this situation, we can not get the correct performance while executing the query. I had a chance to see the query execution plan and did some analysis.

I have executed the script when stretch db is not enabled. I took a simple table without any index on it.

image

 

Then I enabled the stretch db and included the above table and got the below query plan. At this time, almost all the records were migrated from local to azure.

image

This was not the execution plan when I see immediately once enabled the stretch. At that time, local table has more records compare than azure.

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

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

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.

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

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