The SQL truncate TABLE statement is used to delete all records in a table. It performs the same function as a DELETE statement without a WHERE clause. SQL Truncate is a data definition language (DDL) command that deletes all rows from a table by unallocating pages that are used to store table data. 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. In SQL Server, there are two ways to delete rows from a table: the TRUNCATE and DELETE commands. Although the end result of both commands is the same, there are very important differences that you should be aware of.
Everyone should know that DELETE is a DML command and TRUNCATE is a DDL command. DELETE deletes records one by one and makes an entry for each and every deletion in the transaction log, while TRUNCATE deallocates pages and creates an entry for page deallocation in the transaction log. The TRUNCATE TABLE statement in SQL is a data definition language (DDL) operation that labels the extensions of a table for DE mapping (empty for reuse). To truncate a partitioned table, the table and indexes must be aligned (partitioned in the same partition function).
WITH (PARTITIONS (2, 4, 6 TO) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated. If I remember correctly, the TRUNCATE statement will delete all rows AND the space used by the rows (in other words, it will deallocate space). Let's explore the difference between the SQL Delete command and the SQL Truncate command in more detail. Truncate is all or nothing: it will delete all rows from the table, so it may not be an option if you want to keep some rows.
The table owner, members of the sysadmin fixed server role, database owner, and DBDDL administrator fixed database roles have TRUNCATE TABLE permissions by default and are not transferable. Tables with more than 128 extensions can be deleted or truncated in Microsoft SQL Server without requiring simultaneous locks on all extensions required for deletion. However, you can use the EXEC TRUNCATE TABLE statement to include the TRUNCATE TABLE statement within a module, such as a stored procedure, and give the module sufficient permissions. To delete all records from the customer_data table, you can include the select query before and after the truncation operation.
TRUNCATE TABLE is similar to the DELETE statement without a WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system log and transaction resources.
