Difference between Stored Procedure and Function in SQL Server

1. Functions must always return a value (either a scalar value or a table). Stored procedures may return a scalar value, a table value or nothing at all.

2. Functions can have only input parameters whereas Stored Procedures can have input/output parameters .

3. Functions can be called from Stored Procedure whereas Stored Procedures cannot be called from Function.

4. Stored Procedure allows SELECT as well as INSERT/UPDATE/DELETE statement whereas Function allows only SELECT statement.

5. Stored Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

6. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.

7. The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.

8. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.

9. Exception can be handled by try-catch block in a Stored Procedure whereas try-catch block cannot be used in a Function.

10. We can go for Transaction Management in Stored Procedure whereas we can't go in Function.

11. Stored procedures are called independently, using the EXEC command, while functions are called from within another SQL statement

How to do INNER JOIN, LEFT JOIN and RIGHT JOIN in LINQ as well as in SQL? Part 1

Today I will show you how to write LINQ Query as well as SQL Query. Both LINQ and SQL examples will clear your idea about INNER JOIN, LEFT JOIN and RIGHT JOIN. And also it will show you different results based on different JOINs.

Let’s create platform for it. Suppose we have following tables with data.

SELECT * FROM CUSTOMER

SELECT * FROM CITY

Now we will write SQL Queries for INNER JOIN, LEFT JOIN and RIGHT JOIN.

--Inner Join
SELECT cust.Name 
FROM CUSTOMER cust
 INNER JOIN CITY ct 
  ON ct.id = cust.city_id
  
--Left Join
SELECT cust.Name
FROM CUSTOMER cust
 LEFT JOIN CITY ct 
  ON ct.id = cust.city_id
 
--Right Join
SELECT ct.name AS City  
FROM CUSTOMER cust
 RIGHT JOIN CITY ct 
  ON ct.id = cust.city_id

After SQL, we will write LINQ Queries in C# for INNER JOIN, LEFT JOIN and RIGHT JOIN.

            
using (MyTestEntities db = new MyTestEntities())
{

 //Inner Join
 var results = (from Customer cust in db.Customers
                join City ct in db.Cities
                on cust.City_Id equals ct.Id
                select cust.Name).ToList();


 //Left Join
 results = (from Customer cust in db.Customers
            join City ct in db.Cities 
            on cust.City_Id equals ct.Id into Joined
            from lj in Joined.DefaultIfEmpty()
            select cust.Name).ToList();


 //Right Join (Just swap it)
 results = (from City ct in db.Cities
            join Customer cust in db.Customers 
            on ct.Id equals cust.City_Id into Joined
            from rj in Joined.DefaultIfEmpty()
            select ct.Name).ToList();

}

Now it's time to see results:

INNER JOIN Result for both queries will be like this

LEFT JOIN Result for both queries will be like this

RIGHT JOIN Result for both queries will be like this

Now you can compare SQL, LINQ and Result to get clear idea about it.

What is the difference between SQL notification and SQL invalidation?

SQL Notification: The SQL cache notification generates notifications when the data of a database changes, on which your cache item depends.

SQL Invalidation: The SQL cache invalidation makes a cached item invalid when the data stored in a SQL server database changes.