Truncating data in SQL is a process of deleting all records from an existing table. It is a data definition language (DDL) statement that flushes an existing table by deleting all records and preserving table columns, privileges, indexes, views, constraints, relationships, etc. Truncating a table is faster than the DELETE statement and does not affect any of the table's indexes, triggers, or dependencies. The SQL TRUNCATE TABLE command is used to delete complete data from an existing table.
It performs the same function as a DELETE statement without a WHERE clause. The TRUNCATE TABLE command removes data from a table, but not the table itself. TRUNCATE TABLE removes all rows from a table, but the structure of the table and its columns, constraints, indexes, etc. remain. To delete the table definition in addition to its data, use the DROP TABLE statement.
The TRUNCATE TABLE statement deletes all rows from a table faster than DELETE. Logically, TRUNCATE TABLE is similar to the DELETE statement without a WHERE clause. The SQL TRUNCATE TABLE server statement is used to delete all records in a table. If you need to add a table name prefix to the database name, you can retype TRUNCATE TABLE as follows:TRUNCATE TABLE [database_name].[table_name]From the above statement, partitions 2, 4, 6, 7, 8 will be truncated leaving the data of the other partitions will not be truncated. However, we must keep in mind that TRUNCATE TABLE statements may not be undone in many SQL databases. To display the SQL truncation error message: “String or binary data would be truncated in the table '%.” you can use this trace flag.
Now that you know how to truncate data in SQL and what it does, you can use it to quickly delete all records from an existing table. Truncating a table can be faster and does not affect any of the table's indexes, triggers, or dependencies.
Leave Reply