Truncate Query, Drop and Rename Query in SQL

Truncate Query

The TRUNCATE TABLE statement, In Structured Query Language (SQL), is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation (purge for reuse).

The result of this operation removes all data quickly from a table. This function typically bypass a number of integrity enforcing and checking mechanisms. It was officially introduced in the SQL 2008 standard.

The TRUNCATE TABLE tbl_Name statement is a logical equivalent to the DELETE FROM tbl_Name statement (without a defined WHERE clause). The following characteristics distinguish TRUNCATE TABLE statement from DELETE statement:

  • In the Oracle Database, TRUNCATE is implicitly preceded, followed by a commit operation. This may also be the case in MySQL Database while using a transactional storage engine. 
  • TRUNCATE TABLE, typically, deletes all records in a table quickly by deallocating the data pages used by the said table. This action reduces the overhead resources of deletion logs, as well as the locks acquired. 

In this way, the records removed, cannot be restored through a rollback operation. Two notable exceptions to this rule are, PostgreSQL and Microsoft SQL Server where these implementations are found. Both databases allow TRUNCATE TABLE statements to be committed or rolled back transitionally.

  • WHERE clause cannot be specified in a TRUNCATE TABLE statement—you either delete all records or no record at all. 
  • TRUNCATE TABLE does not work when foreign key references of the tables are also to be truncated because TRUNCATE TABLE statements cannot fire triggers. 

This limitation could result in an inconsistent data because ON DELETE and ON UPDATE triggers will not fire.

  • TRUNCATE TABLE, in some databases, resets the count of an Identity_column back to the identity_seed. 
  • In Microsoft SQL Servers, every change to the database is logged. Therefore TRUNCATE TABLE statements can be used for tables involved in log shipping.

Drop Table

DROP TABLE statement, in SQL, is used to drop or remove a table along with all the data, triggers, indexes, constraints, and permission specifications for that table.

Syntax:

The basic syntax for DROP TABLE statement, is as follows:

DROP TABLE tbl_name; 

Example:

Before executing the above command, first verify USERS table and then we would delete it from the database:

SQL> DESC USERS;
===========================================================
| Field   | Type          | Null | Key | Default | Extra |
| ID      | int(11)       | No   | PRI |         |       |
| NAME    | varchar(20)   | No   |     |         |       |
| UName   | varchar(20)   | No   |     |         |       |
| PWord   | varchar(25)   | Yes  |     | Null    |       |
| Status  | Int(1)        | Yes  |     | 1       |       |
===========================================================
5 rows in set (0.00 sec)

This means that USERS table exists in this database, so let us drop it as follows:

SQL> DROP TABLE USERS;
Query OK, 0 rows affected (0.01 sec)

Now, if we again try DESC command, we would get a following error:

SQL> DESC USERS;
ERROR 1146 (42S02): Table 'TEST.USERS' doesn't exist

Here, the database name used in this example is TEST.

Rename

RENAME TABLE permits you to rename a current table in any database. To rename a table, you should either be the db owner or the table owner.

Syntax

RENAME table-Name TO new-tbl-Name

On the off chance that there is a perspective or remote key that references the table, endeavors to rename it will produce a blunder. What’s more, if there are any check imperatives or triggers on the table, endeavors to rename it will likewise create a blunder.

RENAME TABLE TEST.USERS TO SYSUSERS

Statement dependency system

In the event that there is a record characterized on the table, the table can be renamed.

The RENAME TABLE articulation is not permitted if there are any open cursors that reference the table that is being modified.

More Related Articles For You

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes

Search

Shortcodes Ultimate

 
Follow Us
 
BGP