SQL Server : Stored Procedure vs Function, Truncate vs Delete

Stored procedure vs Function

Stored Procedure	Function
Stored procedures are stored in parsed and compiled format in the database.	Functions are compiled and executed at run time.
Stored procedures can not be called in SQL statement like Select.	Functions can be called in SQL statement like Select.
Stored procedures are usually used for Business logic.	Functions are normally used for computations.
Stored procedures can contain DML statements.	Functions can not contain DML statements.
Exception can be called in stored procedure by TRY-CATCH Block.	Exception can not be used in Function
Stored Procedure may or may not return a value.	Function will always return a value & it can be only single value.
Transaction management is possible in Procedure	Transaction management Is not possible in Function
A Function can be called inside a stored procedure	A stored procedure can not be called inside a function.

Truncate vs Delete

Truncate	Delete
Truncate is a DDL command.	Delete is a DML command.
We can not specify filter in WHERE clause.	We can specify filter in WHERE clause.
Truncate deletes all row from Table.	Delete deletes row based on WHERE condition or can delete whole table.
Truncate is faster as compared to delete	Delete is slower as compared to Truncate.
No log is maintained for Truncate for rows, Log is maintained for data pages.	log is maintained for Delete for each row.
Truncate can not activate a trigger.	Delete can activate trigger.
It resets the identity column.	It does not resets the identity column.

Note :- Delete maintains a ROLLBACK tablespace which stores data which is deleted, so when you exceute ROLLBACK data is gets back. In case of TRUNCATE table does not maintain such ROLLBACK tablespace therefore it does not return data after ROLLBACK