Get benefited from interview Q/A and Online Test section. Subscribe to this blog to get updates directly in your mail box.
Best View in Google Crome, Mozilla firefox or IE 7 or above

Tuesday, March 16, 2010

Drop Delete Truncate in SQL Server

Drop, Delete, and Truncate commands are used on a Table in SQL Server.

Delete Command in SQL:

Delete Command is used to delete records from a table.

Syntax: DELETE FROM Table_Name [WHERE Condition]

Note that the where condition is optional and the rows are deleted based on the where condition.

It means that the row with EmpID=50 will be only deleted.

If there is no Where condition, the entire data will be deleted from that table.
Ex: DELETE FROM Employee
It will delete all the records from Employee table.

Truncate Command in SQL:

Truncate Command is used to delete all the records from a table and frees the space in disc.

Syntax: TRUNCATE TABLE Table_Name


Difference between Delete and Truncate Command:

Delete Command deletes records from a table based on a condition or deletes all the records if no condition is specified. But it doesn't free the space.
Truncate Command deletes all the records from a table as well as frees up the space.

Drop Command in SQL:

Drop command is used to remove an Object from a database. When Drop Table command is used, it deletes all rows from that table, then removes the table from the database and removes all references to that table.

Syntax: DROP TABLE Table_Name
Ex: DROP TABLE Department

Difference between Drop and Truncate Command in SQL:

Truncate Command removes all rows from a table, but the table structure, all relationships, constraints remain in the Database.
But Drop Command removes the entire table, all relationships associated with that table, all constraints in that table.
We can insert new records in a truncated table. But it is not possible when a table is dropped as it doesn't exist.

No comments:

Post a Comment

  © Blogger templates Shiny by 2008

Back to TOP