Wednesday 13 March 2013

difference between stored procedures and functions ?

-> Procedure can return zero or n values whereas function can return one datatype value.
-> Returning a value is optional in Stored Procedure where as it is mandatory w.r.to a function.
-> Functions can be called from  procedure whereas procedures cannot be called from function.
-> Procedures can have both input/output parameters for it whereas functions can have only input parameters.
-> Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
-> We can go for transaction management in procedure whereas we can't go in function.
-> Procedure allows select as well as DML(insert,delete,update) statement in it whereas function allows only select statement in it.

Monday 11 March 2013

difference between truncate, delete and drop command in sql?

this is one of the most frequently asked question in interviews

The DELETE command is used to remove rows from a table and DELETE operation is temporary you need to either COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. By using DELETE we can delete only selected rows as per our requirement.

TRUNCATE removes all rows from a table. The operation cannot be rolled back. TRUNCATE is faster and doesn't use as much undo space as a DELETE.
The DROP command removes a table and its related indexes and privileges from the database.The operation cannot be rolled back.
DELETE and TRUNCATE both are DML commands where as DROP is a DDL command.