Difference between Function and Store procedure

Function	Store procedure
Function must return a value	Store procedure can return a value or no value
Function can have only input parameters but no output parameters	Store procedures can have input parameters as well as output parameters
A Function can be called in a store procedure	A store procedure cannot be called in a function
Function only allows select statement	Store procedure allows select statement as well as DML(Insert,Update,Delete)statements
Function can be used in a select statement	Store procedure cannot be used in the select statement
Function are complied every time when called	Store procedure compiled only once in first call, After compilation store procedures are stored in memory and then called from the memory
We cannot implement try catch block in functions	We can implement try catch block in store procedures
We cannot manage transactions in functions	we can manage transactions in store procedures

Delete Duplicate Record from a table

Delete Duplicate Record from a table

1. Declare a temporary table  @table.
2. Create two fields 'id' as int identity and 'data' as varchar.
3. Insert four records with values 'not duplicate row', 'duplicate row' and 'duplicate row'.
4. Create a while loop with a condition 1=1 and break the loop if zero row count found means no duplicate record exists in the table now.
5. In the query we have used 'GROUP BY' and 'COUNT' aggregate function to calculate the duplicate records.
6. 'GROUP BY' is used for grouping data values having count more than 1.
7. Selecting MAX(id) means getting id of higher number. 
8.  When while loop will run first time it will group three duplicate records with values 'duplicate row' and get its max id like here max id will be 4 and delete the fourth record.
9. In WHILE loop it calculate the Row count by '@@Rowcount' but here row count is not equal to zero than again while loop will run and delete the third record and again row count not equal to zero.
10. While loop run again but not able to get any duplicate row. This time row count will be 0 and it will break the loop and come out.

DECLARE @table TABLE(
      id INT IDENTITY(1, 1)
    , data VARCHAR(20)
    )

INSERT INTO @table VALUES ('not duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')

SELECT  * FROM @table

WHILE 1 = 1
   BEGIN
      DELETE   FROM @table
      WHERE    id IN (SELECT   MAX(id)
                        FROM     @table
                        GROUP BY data
                        HAVING   COUNT(*) > 1)

      IF @@Rowcount = 0
         BREAK ;

   END

SELECT  * FROM @table
  

Sql Joins

Sql Joins

• Sql joins are used to get data from two or more tables according to the join condition.
• We implement primary foreign key relationships on the tables and according to this relationships we make joins on the tables and get data from more than one table.

 Types of Joins

We have three types of joins in sql server

1. Inner Join
2. Outer Join
3. Self  Join

Inner Join :

In Inner Join returns only those rows that have a match in both joined tables.

Syntax : 

SELECT t1.column_name,t2.column_name

FROM table_name1 t1

INNER JOIN table_name2 t2

ON t1.column_name=t2.column_name

Example :

SELECT c.CustomerName,c.LastName,I.InvoiceNo

FROM CustomerDetails c

INNER JOIN InvoiceDetails I

ON u.CustID=I.CustID

Output of the above query is :
 

CustmerName	LastName	InvoiceNo
Kiran	rattan	534524
Bhavnishx	rattan	213424
Prince	Kumar	323476
Prince	Kumar	543233
Kiran	rattan	238756

 

Types of Inner join

Equi Join
Natural Join
Cross Join


Equi Join

The Equi Join returns only those rows that have a match in both joined tables.
In Equi join we use "=" sign between the condition. If cannot use other operators in Equi joins.
Syntax

SELECT t1.column_name,t2.column_name

FROM table_name1 t1

INNER JOIN table_name2 t2

ON t1.column_name=t2.column_name

Example

SELECT c.CustomerName,c.LastName,I.InvoiceNo

FROM CustomerDetails c

INNER JOIN InvoiceDetails I

ON u.CustID=I.CustID

Output of the above query is :
 

CustmerName	LastName	InvoiceNo
Kiran	rattan	534524
Bhavnish	Bakshi	213424
Prince	Kumar	323476
Prince	Kumar	543233
Kiran	rattan	238756

Cross Join

Cross join basically a Cartesian product of the tables that involved in the join.
Resulted rows basically the number of rows in one table multiplied by the number of rows in the other table.

Syntax

SELECT * FROM table_name1

CROSS JOIN table_name2

OR

SELECT * FROM table_name1,table_name2

Example

SELECT * FROM CustomerDetails

CROSS JOIN InvoiceDetails

Output is following :

CustID	CustomerName	FirstName	LastName	InvoiceID	InvoiceNo	CustID
1	KiranRattan	Kiran	Rattan	1	45224	1
1	KiranRattan	Kiran	Rattan	2	653424	2
1	KiranRattan	Kiran	Rattan	3	347776	3
1	KiranRattan	Kiran	Rattan	4	653233	3
1	KiranRattan	Kiran	Rattan	5	348756	1
2	BhavnishBakshi	Bhavnish	Bakshi	1	543224	1
2	BhavnishBakshi	Bhavnish	Bakshi	2	213424	2
2	BhavnishBakshi	Bhavnish	Bakshi	3	977776	3
2	BhavnishBakshi	Bhavnish	Bakshi	4	323233	3
2	BhavnishBakshi	Bhavnish	Bakshi	5	998756	1
3	PrinceKumar	Prince	Kumar	1	543224	1
3	PrinceKumar	Prince	Kumar	2	213424	2
3	PrinceKumar	Prince	Kumar	3	977776	3
3	PrinceKumar	Prince	Kumar	4	323233	3
3	PrinceKumar	Prince	Kumar	5	998756	1

Outer Joins

1. Left Outer Join
2. Right Outer Join
3. Full Outer Join



 
Left Outer Join

• This Join returns all records from the left table and the matched records from the Right table.
• Left table means the table left in the join
• Right table means the table Right in the join

Syntax

SELECT Column_List FROM table_name1 t1

LEFT OUTER JOIN table_name2 t2

ON t1.column_name=t2.column_name

 

Right Outer Join

• This Join returns all records from the Right table and the matched records from the Left table.

Syntax

SELECT Column_List FROM table_name1 t1

RIGHT OUTER JOIN table_name2 t2

ON t1.column_name=t2.column_name

 

Full Outer join

• This join returns all the records matching and non matching from both the tables.

Syntax

SELECT Column_List FROM table_name1 t1

FULL OUTER JOIN table_name2 t2

ON t1.column_name=t2.column_name

Left Excluding JOIN

 This will return all rows of the left table which do not match with any record of the right table.

SELECT *
FROM Table_1 A
LEFT JOIN Table_2 B
ON A.Id = B.UserId
WHERE B.UserId IS NULL

Right Excluding JOIN

  This will return all rows of the right table which do not match with any record of the left table.

SELECT *
FROM Table_1 A
LEFT JOIN Table_2 B
ON A.Id = B.UserId
WHERE A.Id IS NULL 

Outer Excluding JOIN

 This will all return  all rows from left table and right table except the matched rows.

SELECT *
FROM Table_1 A
LEFT JOIN Table_2 B
ON A.Id = B.UserId
WHERE A.Id IS NULL OR 
B.UserId IS NULL

Self Join

• This Join returns records having some relation with the other records in the same table

Example

Id	Name	CaptainId
1	Tejpal	2
2	Suresh	4
3	Mahesh	2
4	Kiran	1
5	Rajender	1
6	Mahendra	3
7	Dheeraj	3

select s2.Name,s1.Name as 'Captain'

from StudentDetails s1

INNER JOIN StudentDetails s2

on s1.Id=s2.CaptainId


Output :

Name	Captain
Kiran	Tejpal
Rajender	Tejpal
Tejpal	Suresh
Mahesh	Suresh
Mahendra	Mahesh
Dheeraj	Mahesh
Suresh	Kiran