Truncate, Delete and Drop Statements in SQL Server

When we want to make some changes in SQL Server database, then we need some statements that can help to do this. SQL Server usages some command i.e.Truncate, Delete and Drop to manipulate its database. But these commands are quite different to each other and it’s working also different.

If you need to perform these statement (Truncate, Delete and Drop) in your database then you must be careful because these statements may get you in trouble.

Truncate Command in SQL Server

The truncate statement is one of the most important statements present in SQL Server. Truncate statement is an example of DDL statement. This statement mostly used to delete the table data permanently. Truncate statement deletes the storage gripped by the table. The deleted storage can be used by the table in future.

The TRUNCATE statement erases entire row from a table, while the structure of table and its column, constraint, index etc. stay unchanged.

The truncate statement can’t be rolled back also you can’t fire any trigger. Truncate statement is faster and does not use undo as delete statement.

Syntax: Truncate table table_name;

Delete Command in SQL Server

Delete statement is used to remove the records forever from SQL server database table. A single Delete statement either delete a single row or number of rows. This MS SQL statement undergoes to DML.In other hands, we can say that Delete statement is used in a data manipulation with respect to schema objects. We can mention the filters with where clause.If you have forgotten the where clause from your query, then the whole table will be deleted every visible record will be cleaned out. If any error happened when we executing the delete statement, then we can roll back to the deletion and restore their records.

To delete particular row from an existing table:

Syntax: DELETE (‘row1’, ‘row2’…) From table_name Where[condition];

To Delete entire row from an existing table follow the given bellow statement:

Syntax: DELETE FROM table_name;

Drop Statement in SQL Server

The drop statement is an example of DDL statement. The drop statement is used to drop the table or entire database from SQL Server database if the database is not required. When you were performing the drop statement at that time you must be sure that you want to delete the particular table or database permanently because drop statement is an irreversible statement, you will lose your data completely. When you execute the Drop statement to your database then you can not fire the triggers.

Syntax: Drop database DatabaseName;

Truncate, Delete and Drop Statements Performance Over to Each Other

I.

2. TRUNCATE statement is unable logged consistently and does not signify.

3. TRUNCATE statements required a metadata grip to execute it.

4. The TRUNCATE statement is totally depending on its versions because it’s typically not logged.

5. Truncate statement is designed to alter how the objects look in the database.

II.

1.DELETE FROM table statement deletes each and every row. If any enabled delete trigger is defined on the table then its action also must be implemented and logged.

2. If indexes occur on the SQL Server database table, then each index must be updated when a row is deleted, and the log should be update for each row.

II.

1. DROP FROM table statement removes the table completely from the database it contains all removing constraints and indexes of related objects.

2. The primary deal with Drop FROM table statement is that, typically you can’t undo it.

Conclusion

In this article we discussed about the Truncate, Delete and Drop statements in SQL Server and also their working. These statements are widely used in the manipulation of the database.