Top 10 differences between Functions and Procedures in SQL

Functions are Procedures are the most common concept used in advanced or complex applications to deal with data effectively and efficiently.

Please find the difference between Functions and Procedures as below:

Procedures	Functions
Procedures may or may not return a value	Functions always return a value
If Procedures returning a value, it can return one or N number of values	Functions return one and only one value
It can have parameters of type input or output	It has only input parameters
To specify the output parameters, we use output parameter	To specify output parameter, we use 'returns' clause
No need to use 'return' statement for returning the value. Assigning value to an output parameter is enough	To return a value we should use the 'return' statement
It allows Transaction management, error handling using Try-Catch and Error-Raising	None of the three are allowed
It allows call a procedure or a function from it	It allows to call functions but not procedures
A procedure cannot be embedded within a SELECT statement and executed	Function can be embedded directly under a SELECT statement and executed
To call Procedure 'EXECUTE' statement is used	To call Function 'SELECT' statement is used
It is used for defining the computer business logic	It is used for defining mathematical calculations

Top 7 differences between DELETE, DROP and TRUNCATE in SQL

Difference between Delete, Drop and Truncate is one of the most confusing concept for most of the people. However, this is the basic concept and easy to remember if understood correctly. Please find the differences with it's respective benefits while working for daily purpose as below:

1. DELETE operates on row-by-row whereas DROP and TRUNCATE works on table level.
2. Triggers get fired after execution of DELETE command whereas no triggers get fired in DROP and TRUNCATE.
3. DELETE and TRUNCATE can be COMMITTED and ROLL-BACKED if provided inside a transaction whereas DROP can’t be COMMITTED OR ROLL-BACKED.
4. TRUNCATE execute faster than DELETE as it works at table level whereas DELETE removes records row-by-row one at a time and an entry get recorded in the Transaction logs.
5. DELETE does not free the space containing the table whereas DROP and TRUNCATE free the space containing the table.
6. WHERE clause can be used in DELETE command whereas in DROP AND TRUNCATE can’t use it.
7. DELETE command keeps the lock over each row whereas TRUNCATE keeps the lock on table not on the entire row and DROP doesn’t keep lock neither at row nor table.