Monday, July 16, 2012

Is Truncate a DDL Statement or DML Statement?

Since operational wise or user experience wise, truncate is equal to DELETE entire table and DELETE is a DML statement, most people think that Truncate is DML statement.

So let us verify this.

Let us create a table to play around.

image

Let me create user with DML permissions.

image

Now using this user let us truncate the employee table.

image

You will end up with an error as shown below.

Msg 1088, Level 16, State 7, Line 2
Cannot find the object "Employee" because it does not exist or you do not have
permissions.

You will end up with an error as shown below.

Now let us create a user with DDL permission.

image

Let us truncate the table using this user.

image

since this is a success Truncate is a DDL statement.

1 comment: