Differenciate between Truncate and Delete.

truncate faster then delete, delete we can where clause not in truncate, delete we rollback but truncate not

1) Trancate is faster than Delete but few draw back is there 2) you have apply where condition in Delete but can not apply where clause in Trancate 3) Delete Records are rollback but trancate records you can not rollback 4

*) TRUNCATE TRUNCATE is a DDL command TRUNCATE is executed using a table lock and whole table is locked for remove all records. We cannot use Where clause with TRUNCATE. TRUNCATE removes all rows from a table. Minimal logging in transaction log, so it is performance wise faster. 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. Identify column is reset to its seed value if table contains any identity column. To use Truncate on a table you need at least ALTER permission on the table. Truncate uses the less transaction space than Delete statement. Truncate cannot be used with indexed views. *) DELETE DELETE is a DML command. DELETE is executed using a row lock, each row in the table is locked for deletion. We can use where clause with DELETE to filter & delete specific records. The DELETE command is used to remove rows from a table based on WHERE condition. It maintain the log, so it slower than TRUNCATE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. Identity of column keep DELETE retain the identity. To use Delete you need DELETE permission on the table. Delete uses the more transaction space than Truncate statement. Delete can be used with indexed views. 3) DROP The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

Trncate is delete method bt use trncate delete only selected row and coloumn... Delete method used total table was deleted...

1. Truncate is more faster than Delete 2.Where clause can not be used in Truncate.

Truncate have not where clause and it reset identity field when Delete have where clause and it do not reset identity field

#TRUNCATE TRUNCATE is a DDL command TRUNCATE is executed using a table lock and whole table is locked for remove all records. We cannot use Where clause with TRUNCATE. TRUNCATE removes all rows from a table. Minimal logging in transaction log, so it is performance wise faster. 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. Identify column is reset to its seed value if table contains any identity column. To use Truncate on a table you need at least ALTER permission on the table. Truncate uses the less transaction space than Delete statement. Truncate cannot be used with indexed views. #DELETE DELETE is a DML command. DELETE is executed using a row lock, each row in the table is locked for deletion. We can use where clause with DELETE to filter & delete specific records. The DELETE command is used to remove rows from a table based on WHERE condition. It maintain the log, so it slower than TRUNCATE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. Identity of column keep DELETE retain the identity. To use Delete you need DELETE permission on the table. Delete uses the more transaction space than Truncate statement. Delete can be used with indexed views.

1. Truncate is a DDL command whereas Delete is a DDL command. 2. Truncate applies Lock on the entire table whereas Delete applies lock on the specific rows which are going to be deleted. 3. Truncate removes all rows from the table, whereas Delete removes the selected rows depending upon the predicate(Condition). 4. Truncate Minimally Logged the Transaction Log and hence are faster but cannot be rolled back, whereas Delete Logs every row in Transaction Log it deletes and are slower but can be rolled back. 5. Truncate RESET the Identity Seed, where as in Delete we have to Reset the identity seed using DBCC CHECKIDENT(‘TableName’, RESEED, 1(Value from which you want to reset)) 6. Truncate cannot be used with the tables having Foreign Key references to any other table, whereas you can use Delete with such tables.