Difference between Drop, Truncate and Delete 

1. DELETE is a DML statement.
2. DELETE removes some rows if WHERE clause is used
3. Can be rolled back
4. Can be used with or without WHERE clause
5. Does not reset identity of the table
6. Triggers will be fired.
7. When DELETE operation is performed, all the data get copied into Rollback Tablespace first, 
and then delete operation get performed. Hence we can get back the data by ROLLBACK command.

SYNTAX:

To delete a particular row

DELETE FROM table_name
WHERE column_name = column_value

To delete all rows

DELETE FROM table_name
Or
DELETE * FROM table_name

DROP

1. DROP is a DDL statement.
2. Removes a table from the database. Table structures, indexes, privileges, constraints will 
also be removed.
3. Cannot be rolled back
4. No Triggers will be fired.

SYNTAX:

DROP TABLE table_name


TRUNCATE

1. TRUNCATE is a DDL Statement.
2. Removes all rows from a table, but the table structures and its columns, constraints, indexes 
remains. 
3. Cannot be rolled back
4. Resets the identity of the table
5. Truncate is faster and uses fewer system and transaction log than delete.
6. Cannot use TRUNCATE on a table referenced by a FOREIGN KEY constraint.
7. No Triggers will be fired.
8. Cannot use WHERE conditions

SYNTAX:


TRUNCATE TABLE table_name

TRUNCATE vs DELETE

TRUNCATE	DELETE
TRUNCATE is a DDL command	DELETE is a DML command
TRUNCATE is executed using a table lock and whole table is locked for remove all records.	DELETE is executed using a row lock, each row in the table is locked for deletion.
We cannot use Where clause with TRUNCATE.	We can use where clause with DELETE to filter & delete specific records.
TRUNCATE removes all rows from a table.	The DELETE command is used to remove rows from a table based on WHERE condition.
Minimal logging in transaction log, so it is performance wise faster.	It maintain the log, so it slower than TRUNCATE.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.	The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
Identify column is reset to its seed value if table contains any identity column.	Identity of column keep DELETE retain the identity
To use Truncate on a table you need at least ALTER permission on the table.	To use Delete you need DELETE permission on the table.
Truncate uses the less transaction space than Delete statement.	Delete uses the more transaction space than Truncate statement.
Truncate cannot be used with indexed views	Delete can be used with indexed views
Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table	Keeps object’s statistics and all allocated space. After a DELETE statement is executed, the table can still contain empty pages.
TRUNCATE TABLE can’t activate a trigger because the operation does not log individual row deletions. When we run truncate command to remove all rows of table then it actually doesn’t removes any row, rather it deallocates the data pages. In case of Truncate triggers will not be fired because no modification takes place, we have just deallocated the data pages not deleted any row from table.	Delete activates a trigger because the operation are logged individually. When we execute Delete command, DELETE trigger will be initiated if present. Delete is a DML command and it deletes the data on row-by-row basis from a table. Which means delete is modifying the data by deleting it from the table. Triggers are fired when a DML statement executed on a table, so trigger will be fired in case of Delete command execution.

Difference between Stored Procedure and Functions

	Stored Procedure	Functions
Compilation	Stored in database in compiled format. Note: Compiled indicates, Execution plan will be made by sql at the time it created and stored in DB.	Will compiled at run time
Return type	It can directly return only integers Return type is not must	It can return any scalar or table Return type is must
Multiple return values	It can also return more than one values (of any data type) indirectly with the help of out parameters	It won't support out parameters
DML Statements	Can have DML statements.	Cannot have DML statements. Note: In case of multi-table valued functions it can contain DML statements affecting Table Variables.
Execution	Stored procedure can execute function. Cannot be the part of Select query as a column. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT	Function cannot execute stored procedure. Can be the part of select query as a column. Functions be used in the SQL statements anywhere in the WHERE/HAVING/SELECT
Exception handling	Can have Try....Catch	Cannot have Try....Catch

Sometimes we face a question, why we can't execute stored procedure inside a function?

Answer:

1. Stored Procedure may contain DML statements.

2. Function can't contain DML statements.
    So executing Function inside stored procedure will never break rule 1.
    But executing stored procedure inside function may break rule no 2.

So ultimately strict rule is made why Sql team, we can't execute stored procedures inside function.

We are thankful to questpond.com for providing this SQL Server interview question with answers.

Below is a nice SQL Server interview question video on Unique key vs primary keys.