What is the difference between “Stored Procedure” and “Function”?
- A procedure can have both input and output parameters, but a function can only have input parameters.
- Inside a procedure we can use DML (INSERT/UPDATE/DELETE) statements. But inside a function we can’t use DML statements.
- We can’t utilize a Stored Procedure in a Select statement. But we can use a function in a Select statement.
- We can use a Try-Catch Block in a Stored Procedure but inside a function we can’t use a Try-Catch block.
- We can use transaction management in a procedure but we can’t in a function.
- We can’t join a Stored Procedure but we can join functions.
- Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section. But we can use a function anywhere.
- A procedure can return 0 or n values (max 1024). But a function can return only 1 value that is mandatory.
- A procedure can’t be called from a function but we can call a function from a procedure.
What is difference between “Clustered Index” and “Non Clustered Index”?