RE: What is the difference among "dropping a table, ...
Dropping table the structure of table as will as row of table .
but truncate table delete all the row of table but structure of table remain same we can apply where condition on it . delete work like truncate the table but we can apply where condition on it. Truncate can't roll back delete can.
RE: What is the difference among "dropping a table, ...
Here is a more complete answer: "dropping a table removes the table definition from the schema of the database and, of course, all the data truncating a table" internally within the database 'marks' the table as empty and this is imporant: the deleted records are not recorded in the transaction log."deleting all records" removes the records from the table and records each deletion in the transaction log
RE: What is the difference among "dropping a table, ...
Here is a more complete answer: "dropping a table removes the table definition from the schema of the database and, of course, all the data truncating a table" internally within the database 'marks' the table as empty and this is imporant: the deleted records are not recorded in the transaction log."deleting all records" removes the records from the table and records each deletion in the transaction log
RE: What is the difference among "dropping a table, ...
Dropping table deletes table definition and records both
Delete commannd deletes all records but records all details in Transaction log file.
Truncate Table provides some special features in addition to delete all records from table . like Delete trigger not fired when it executes, Reset table identity. only page deallocation details send to Transeaction logs
RE: What is the difference among "dropping a table, ...
DROP <Table>: Drops the table records and structure from the DB schema. Nothing else is leftout. Can't rollback.
Truncate <Table>: Clears the records from the table but the table structure still remains. Can't roll back.
delete from <table>: deletes the rows based on the query or will delete all the rows if no "where" clause is given. Can roll back i.e deleted records can restored.
In the first two cases transaction log is not done, but with delete transaction log is created. So it consumes more time compared to the above two.
RE: What is the difference among "dropping a table, ...
Dropping a table will drop the table schema from the database, there wont be any more record of that table.Deleting a row will delete row from certain table with entry in log file for every row deleted, which we can rollback., it will not reset identity value of identity columnTruncating a table will delete all the rows from the table, and will make only one entry in log file for deleting all the row, THAT ALSO WE CAN ROLL BACK. Also it will reset Identity value of the identity column.DIFFERENCE BETWEEN DELETE AND ROLLBACK IS THAT : in delete it will make one entry for each row to be deleted. while truncate will make only one entry for all the rows. in delete it will free all the datapages by deleting the contents of the data pages. while in truncate it will delete the pointer to those datapages for all deleted rows.Also we can not use truncate if we have column in that table, which is referenced in other table. But we can use delete if we provided cascade options while creating tables.
RE: What is the difference among "dropping a table, ...
Hi,
As You have written like you can apply where condition on Truncate, but we can not apply where clause. So, kindly be careful before posting any messages.
RE: What is the difference among "dropping a table, ...
Truncating a table is just remove all the records from the table.'where' clause is not used in truncating a table and also we cannot rollback the table.In delete option we can use WHERE clause to delete the rows and also we can rollback the deleted table.
