7 Differences Between SP and FN

1. Functions must return a value(scalar,inline table or multi statement table) whereas stored procedure may or may not retun a value.
2.Functions can return a table whereas stored procedures can create a table but can't return table.
3. Stored procedures can be called independently using exec keyword whereas function are called using select statements.
4. Stored procedure can be used to change server configuration(like setting permissions ) whereas function can't be used for this.
5. XML and output parameters can't be passed to functions whereas it can be with sp's.
6. Transaction related statement can be handled in sp whereas it can't be in function.
7. Stored procedures can call a funtion or another stored procedure similarly a function can call another function and a stored procedure.The catch with function is that no user defined stored proc can be called.Only extended/system defined procs can be called.


Thanks..

Find nth highest salary..

How to find the nth highest salary to salary table??
I have faced many times this question in interviews, I think,
it is the most popular question asked in interview.

Read many articles on Internet and found that following is the best one to find Nth highest salary using TOP clause.

Suppose we want to find 3rd highest salary, then:-

Create Table Salary
(
EmpID Int,
Salary Int
)

Insert Into Salary
Select 101, 25000
UNION
Select 102, 45000
UNION
Select 103, 75000
UNION
Select 104, 5000
UNION
Select 105, 3000
UNION
Select 106, 2000
UNION
Select 106, 97000

Select * From Salary
Order By Salary Desc

--Identify
Select Top 1 Salary
From
(
Select Top 3 Salary
From Salary
Order By Salary Desc
) aa
Order By Salary


OUTPUT : 45000

So simply using this SQL we can find Nth highest salary to change the Second Top clause.

Thanks...

Delete Duplicate Record using CTE?

Hi All,
I know there are many ways to delete duplicate records to table, but some times we find that there is no column to identity each record like Identity or any PK.
So handle this requirement , I think CTE is best option here..
Try sql:

----Delete Duplicate
Create Table Product
(
Product Varchar(100),
Price Int
)

Insert Into Product
Select 'Sugar','100'
UNION ALL
Select 'Salt','50'
UNION ALL
Select 'Refined','300'
UNION ALL
Select 'Sugar','100'
UNION ALL
Select 'Salt','50'
UNION ALL
Select 'Juice','75'

UNION ALL
Select 'Juice','75'

Select *,'Before' from Product;

WITH CTE (ID,Product,Price)
AS
(
Select ROW_Number () OVER(Partition By Product Order By Product) AS ID,*
from Product
)
Delete
From CTE
Where ID >1;

Here Row_Numer function generates unique number for each partition and then we can simply delete the records having ID value > 1 and solve our problem.

Thanks...