TRUNCATE

Question

TRUNCATE

Viewing 15 posts - 16 through 30 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 1

Christian Buettner-167247 (2/5/2010)
I wonder why there is no DDL trigger available to capture this of event...

Because it is a DML event, the structure of the table is not changing, just the data in it? Sound right experts?

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 2

SanjayAttray (2/6/2010)
Back to basics. Most of the time I select wrong answer for basic and simple question. Triggers are for INSERT, UPDATE, or DELETE only.

For DML, yes...

Or... CREATE, ALTER, etc.... for DDL...

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 3

petertrast (3/3/2010)
SanjayAttray (2/6/2010)
Back to basics. Most of the time I select wrong answer for basic and simple question. Triggers are for INSERT, UPDATE, or DELETE only.
For DML, yes...
Or... CREATE, ALTER, etc.... for DDL...

OK, I promise to read all posts before replying 😀

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

vk-kirov SSCertifiable Points: 7686 More actions · Answer 4

petertrast (3/3/2010)
Christian Buettner-167247 (2/5/2010)
I wonder why there is no DDL trigger available to capture this of event...
Because it is a DML event, the structure of the table is not changing, just the data in it?

By the way, Oracle documentation contains an explicit list of DDL statements: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_1001.htm#sthref2301. TRUNCATE is in that list. So, at least in Oracle it is not a DML event.

Unfortunately, I didn't find the similar list for MSSQL. However, these two quotes from BOL implicitly say that TRUNCATE belongs to DDL:

http://msdn.microsoft.com/en-us/library/ms177570.aspx

TRUNCATE TABLE (Transact-SQL)
Permissions
--------------------------------------------------------------------------------
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles

OK, who's that guy with db_ddladmin permissions? http://msdn.microsoft.com/en-us/library/ms189121.aspx

Database-level role name: db_ddladmin
Description: Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

So, db_ddladmin can run any DDL command; db_ddladmin can run TRUNCATE TABLE. This means TRUNCATE is a DDL command, I believe 🙂

Paul White SSC Guru Points: 150467 More actions · Answer 5

vk-kirov (3/3/2010)
So, db_ddladmin can run any DDL command; db_ddladmin can run TRUNCATE TABLE. This means TRUNCATE is a DDL command, I believe 🙂

Crows are birds.

All crows are black.

Therefore, all birds are black.

Oh dear.

:laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 6

vk-kirov (2/10/2010)
In the example above, we will see request_mode = Sch-M (schema modification). This is specific to DDL events.

Not true. From Books Online - Lock Modes:

Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.

So, table truncation is explicitly defined as a DML operation.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 7

Paul White NZ (3/30/2010)
vk-kirov (3/3/2010)
So, db_ddladmin can run any DDL command; db_ddladmin can run TRUNCATE TABLE. This means TRUNCATE is a DDL command, I believe 🙂
Crows are birds.
All crows are black.
Therefore, all birds are black.
Oh dear.
:laugh:

Impeccable logic Paul 😉

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

Paul White SSC Guru Points: 150467 More actions · Answer 8

Peter Trast (3/30/2010)
Impeccable logic Paul 😉

Given the bird theme, I suppose it should be im-peck-able logic really 😀

(sorry)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 9

Paul White NZ (3/30/2010)
Peter Trast (3/30/2010)
Impeccable logic Paul 😉
Given the bird theme, I suppose it should be im-peck-able logic really 😀
(sorry)

Booooo.... lol...

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

vk-kirov SSCertifiable Points: 7686 More actions · Answer 10

Paul White NZ (3/30/2010)
Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.
So, table truncation is explicitly defined as a DML operation.

Nice find, thank you.

So, if it barks like a dog, it behaves like a dog, what is it?..

...it may be a duck in SQL Server 😀

Paul White SSC Guru Points: 150467 More actions · Answer 11

vk-kirov (3/31/2010)
Nice find, thank you. So, if it barks like a dog, it behaves like a dog, what is it?...

It is a duck, with some arguably dog-like features 😛

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 12

Paul White NZ (3/31/2010)
vk-kirov (3/31/2010)
Nice find, thank you. So, if it barks like a dog, it behaves like a dog, what is it?...
It is a duck, with some arguably dog-like features 😛

Interesting de-duck-tion....

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

Paul White SSC Guru Points: 150467 More actions · Answer 13

Peter Trast (3/31/2010)
Paul White NZ (3/31/2010)
vk-kirov (3/31/2010)
Nice find, thank you. So, if it barks like a dog, it behaves like a dog, what is it?...
It is a duck, with some arguably dog-like features 😛
Interesting de-duck-tion....

Boooooo! 😛

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 14

Paul White NZ (3/31/2010)
Peter Trast (3/31/2010)
Paul White NZ (3/31/2010)
vk-kirov (3/31/2010)
Nice find, thank you. So, if it barks like a dog, it behaves like a dog, what is it?...
It is a duck, with some arguably dog-like features 😛
Interesting de-duck-tion....
Boooooo! 😛

Hey, people from middle earth can't boo! 😀

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

Paul White SSC Guru Points: 150467 More actions · Answer 15

Peter Trast (3/31/2010)
Hey, people from middle earth can't boo! 😀

Middle Earth, if you please 😛

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi