SQL- Temporary table in SQL SERVER

Temporary table in SQL SERVER

Temporary table: We create temporary table for temporary storing of data as the name suggests. It is very similar to normal tables and one can perform all operations on temporary 
table.Temprory tables are created inside tempdb database.The lifetime or the scope of 
the temporary table is limited.There are two kinds of temporary table which are as 
follows:

Local temporary table: It is prefixed with # to the table name.It is visible  only to the one who has created it till the session  exists. Table gets automatically deleted when the user log out from the session. The user can also explicitly drop the temporary table by using DROP command.
   Following below query shown is an example to create local temporary  table.

Creating Local Temporary Table

CREATE TABLE #Temp
(
  ID INT,
  Name VARCHAR(30),
  Age INT

  )

Inserting values into Local Temporary Table

INSERT INTO #Temp VALUES(1,'Sunil',24)
INSERT INTO #Temp VALUES(2,'Shweta',22)
INSERT INTO #Temp VALUES(3,'Rohit',25)
INSERT INTO #Temp VALUES(4,'Sohan',21)
INSERT INTO #Temp VALUES(5,'Mohan',21)

Let us see the data in #temp

SQL- Difference between Local temporary table and Global temporary table

Q. What is the difference between Local temporary table and Global temporary  table ?

Local temporary table: It is prefixed with # to the table name.It is visible  only to the one who has created it till the session  exists. Table gets automatically deleted when the user log out from the session. The user can also explicitly drop the temporary table by using DROP command.
   Following below query shown is an example to create local temporary  table.

CREATE TABLE #Temp
(
  ID INT,
  Name VARCHAR(30),
  Age INT

  )

Global temporary table: It is prefixed with ## to the table name. It is visible to the multiple users once the connection or session has been created.Table gets automatically deleted when all the user referencing to that table logs out from the session.The user can also explicitly drop the temporary table by using DROP command.
      Following below query shown is an example to create global temporary  table.

CREATE TABLE ##Temp1
(
  ID INT,
  Name VARCHAR(30),
  Age INT

  )

Click for detail

SQL Questions PART-2

Q What are different types of constraint in SQL ?

    Following are the different types of constraints in SQl:-

•     Primary Key Constraint
•     Foreign Key Constraint
•     Unique Constraint
•     Check Constraint
•     Not Null Constraint

Q What is self referencing Foreign Key ?
        When a foreign key refers to the column of the same table then it is  called self referencing foreign key.

Q what is aggregate function ?

       Aggregate function returns single value after performing calculation on        set of values.Aggregate functions are generally used with the GROUP BY        clause of the SELECT statement.

•     AVG() - It is used to find the average value
•     COUNT() - It is used to find the number of rows
•     MAX() - It is used to find the largest value
•     MIN() - It is used to find the smallest value
•     SUM() - It is used to find the sum

SQL-Deleting Duplicate Records from a Table

Q How to delete duplicate records from a table ?
      This is one of the famous question asked by  an interviewer. Lets us see how to to delete duplicate records from a table. Table StudentInfo has duplicate records as shown below.

SudentInfo Table

Step 1: First we will create a temporary table say temp and copy all original table (StudentInfo Table) data into this very temporary table.