Why delete is dml and truncate is ddl

This probably comes from the identity reset since we're. One other important aspect of this Sch-M lock is that while Truncate is in progress you can't modify a table in any way. You can't update or insert into a table from another transaction contrary to when deleting data when you can still update or insert from another transaction. Home SQLTeam.

Truncate: - deallocates the data pages in a table and only this deallocation is stored in transaction log - aquires only table and page locks for the whole table. 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. Remove From My Forums. Answered by:. Archived Forums. This is the place for advice and discussions 5 5.

Sign in to vote. Thanks Sarat. Thursday, November 8, AM. Truncate: - deallocates the data pages in a table and only this deallocation is stored in transaction log - aquires only table and page locks for the whole table. Well yes This probably comes from the identity reset since we're modifying a table property which counts as schema. Friday, November 9, PM.

Olaf Helper Blog Xing. Shakeer Hussain. The below link says that Truncate is a DML command. Friday, November 9, AM. Mureinik Mureinik k 49 49 gold badges silver badges bronze badges. A simple practical explaination can be : Truncate reinitializes the identity by making changes in data definition therefore it is DDL , whereas Delete only delete the records from the table and doesn't make any changes in its Definition that's why it is DML.

Now Truncate Table and Reinsert the records. 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. The Overflow Blog. Podcast Explaining the semiconductor shortage, and how it might end.

Does ES6 make JavaScript frameworks obsolete? Featured on Meta. Now live: A fully responsive profile. Linked 2. Related Hot Network Questions. Question feed.