Why is truncate a ddl command

Improve this question. Ravi Ravi 1, 3 3 gold badges 15 15 silver badges 20 20 bronze badges. Comments are not for extended discussion; this conversation has been moved to chat. Add a comment. Active Oldest Votes.

So I'd sum it up as: truncate is not "transactional" in the sense that it commits and can't be rolled back, and can modify object storage attributes. Improve this answer. Mat Mat 9, 3 3 gold badges 40 40 silver badges 40 40 bronze badges. When you create a table, you can specify things like what tablespace it goes in, initial size, next extent size, etc.

Those are storage attributes. Yes, they're part of a table's or index, or partition metadata. Tushardeep Singh Tushardeep Singh 1.

Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. After all we are modifying the data, aren't we?

This probably comes from the identity reset since we're modifying a table property which counts as schema. Truncate is a DDL, Truncate does not modify the data. Truncate never touches the data, thats a misconception. In every instance of SQL Truncate does the same thing, it simply marks the extents and lets them know the pages are to be reused deallocated. The pages then after commit are ready for use by the system. This is also why you cannot use TRUNCATE with a "where" clause, your not deleting groups of data IN a table, your marking the data pages that make up that table to be reused by the server.

Delete is DML because it removes and logs each individual piece of data. We're not really touching the data and doing things to it, we're touching the containers for the data, and metadata schema. Compound Statement Syntax. Variables in Stored Programs. Local Variable Scope and Resolution. Flow Control Statements. Restrictions on Server-Side Cursors. Restrictions on Condition Handling.

Database Administration Statements. Account Management Statements. Resource Group Management Statements. Table Maintenance Statements. Component, Plugin, and Loadable Function Statements. Other Administrative Statements. InnoDB Cluster. InnoDB ReplicaSet. Error Messages and Common Problems.