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
- Architecture of DBMS
- CODDS RULE DBMS
- Database Models in DBMS
- Relational DBMS Concepts
- Keys and Types of keys in Database
- Database Normalization
- Generalization, Specialization and Aggregation Concepts in DBMS
- ERD Diagram Tutorial with Examples in DBMS
- Introduction to SQL
- How to Create Query in SQL, Create Table, Delete Table and User Insert Info Statements
- Alter Query Command in SQL, Add, Modify and Drop Column in Table
- TCL Commands in SQL
- All DML Statement in SQL, Select Statement, Delete, Insert and Update Statement
- Data Control Language DCL Revoke and Grant Command in SQL
- Select Statement or Select Query in SQL Explained with Examples
- Distinct Keyword Explained in SQL
- WHERE Statement or WHERE Clause in SQL Statement Explained
- AND & OR Operators in SQL
- LIKE Operator in SQL
- ORDER BY Clause Sorting Explained in SQL
- Group By Clause in SQL
- Having Clause Explained in SQL
- SQL Constraints
- SQL Aggregate Functions
- SQL Aliases Use and Purpose in SQL
- SQL Joins Types, Use and Purpose
- SQL Sequence Syntax and Use with Examples
- SQL View
- SET Operation in SQL, UNION, UNION ALL, Intersect and Minus