SQL Server Global Temprary Tables

Global Temporary table is physical table which is created for temporary purpose to hold data.Global Temporary table starts with##. Global Temporary table is not user or session specific. Once a Global Temporary table is created, the table remain until a drop command is executed or connection is broken. When a create table script is executed for a Global Temporary, physical table is created on tempdb database. You can see the tables as follows

“Object Explorer -> Databases -> System Databases-> tempdb -> Temporary Tables”

A  Global Temporary table is visible to all user, a particular user can create a Global Temporary table, but it will be visible to all user. When connection is broken with databases, Global Temporary table disappear automatically.


Here is the syntax of Global Temporary table.

Syntax

CREATE TABLE ##table_name
(
  column1 datatype NULL/NOT NULL,
  column2 datatype NULL/NOT NULL,
  column3 datatype NULL/NOT NULL,
  ...
);

You can do data manipulation operation like insert, update, delete with Global Temporary table. Below is the example of how a Global Temporary table is created and data manipulation can be done in Global Temporary table.


Create Table

create table ##myTable

(

    ID int,

    Name Varchar(50),

    Roll Varchar(50)

)

Create Table and Insert data

create table ##myTable

(

ID int,

Name Varchar(50),

Roll Varchar(50)

)

GO

INSERT INTO ##myTable(ID,Name,Roll)

SELECT 1,'Student1',123

UNION

SELECT 2,'Student2',124

UNION

SELECT 3,'Student1',125

Output

(3 row(s) affected)

Create Table and Update data 

create table ##myTable

(

ID int,

Name Varchar(50),

Roll Varchar(50)

)

GO

INSERT INTO ##myTable(ID,Name,Roll)

SELECT 1,'Student1',123

UNION

SELECT 2,'Student2',124

UNION

SELECT 3,'Student1',125

GO

UPDATE ##myTable

SET Name='New Srudent'

WHERE ID=1

SELECT * FROM ##myTable

Output

ID    Name         Roll

1     New Srudent  123

2     Student2     124

3     Student1     125

Create Table and Delete data

create table ##myTable

(

ID int,

Name Varchar(50),

Roll Varchar(50)

)

GO

INSERT INTO #myTable(ID,Name,Roll)

SELECT 1,'Student1',123

UNION

SELECT 2,'Student2',124

UNION

SELECT 3,'Student1',125

GO

DELETE FROM ##myTable

WHERE ID=1

GO

SELECT * FROM ##myTable

Output

ID    Name         Roll

2     Student2     124

3     Student1     125

Global Temporary table can be used for joining purpose. Joining operation can be done with  physical tables, table variable ect. Below is the example of joining of Global Temporary table.

create table ##myTable

(

ID int,

Name Varchar(50),

Roll Varchar(50)

)

create table student

(

ID int,

Name Varchar(50),

Roll Varchar(50)

)

SELECT a.Name,b.Name

FROM student a

INNER JOIN ##myTable b

ON a.ID=b.ID

Global Temporary table can be used in a store  procedure. Below is the example of Global Temporary table used in stored procedure.


CREATE PROCEDURE sp_my_procedure

            @LastName nvarchar(50),

            @FirstName nvarchar(50)

AS

create table ##myTable

(

         ID int identity primary key ,

         Name Varchar(50),

         Roll Varchar(50),

         createtime datetime DEFAULT CURRENT_TIMESTAMP

)

create table student

(

        ID int,

        Name Varchar(50),

        Roll Varchar(50)

)

SELECT a.Name,b.Name

FROM student a

INNER JOIN ##myTable b

ON a.ID=b.ID

GO

one of the major difference with temp table and Global temporary table. Global temporary table are not user a dependent, it does not depend on session, only depend on connection. Till the connection is active the Global temporary table is available in the database until drop moment is not executed

SQL Server Primary Key

Primary Key maybe combination of single or multiple column , that define uniqueness of value in a table.

           Primary key guaranteed that no duplicate value should we enter in the table. While inserting data into the table, primary key check that same value already is there or not, if same value exits , primary key prevent entering the data and raise an error message. 


The as primary key create an unique record for a table. Primary key does not allow null , a null is not a value or absent of  value. If you create primary key with the help of combination multiple column it is called composite primary key. 


Below is the syntax of creating primary key. 

CREATE TABLE table_name
(
  column1 datatype primary key,
  column2 datatype NULL/NOT NULL,
  column3 datatype NULL/NOT NULL,
 
);
 

Below a table is created with primary key.

create table myTable

(

ID int identity primary key ,

Name Varchar(50),

Roll Varchar(50),

)

Below a table is created with multiple primary key. 

CREATE TABLE STUDENT

(

ID int ,

Roll int,

RegNo int,

Name Varchar(50)

PRIMARY KEY (ID, Roll, RegNo)

)

Below is the example of alter table with primary key. 

CREATE TABLE STUDENT

(

ID int NOT NULL ,

Roll int,

RegNo int,

Name Varchar(50)

)

GO

alter table STUDENT add primary key (ID)

Below is the example of script to drop primary key from a Table.

ALTER TABLE STUDENT

DROP PRIMARY KEY