SQL- Shortcut way to delete duplicate records from a table

I have discussed earlier in one of my post how to delete duplicate records from a table . But recently i came up with even shorter way to delete duplicate records.

Consider a StudentInfo Table as shown  below for demonstration.

SELECT * FROM StudentInfo

StudentInfo Table

Step1:
 First we will create a temporary table '#temp' with same structure  as that of original table 
'StudentInfo' and insert all distinct rows from the 'StudentInfo' table into temporary table as follows:

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