• Well, I got it wrong. Since TRUNCATE TABLE is listed under DDL commands in BOL, while the BOL page on Lock Modes clearly states that TRUNCATE TABLE is a DDL command, it is clear that according to BOL TRUNCATE TABLE is both a DDL command and a DML, so according to BOL 6 of the answers are correct and we are playing "guess which 3 of the 5 correct options you are supposed to choose". That doesn't mean I think there's anything wrong with the question, or indeed with the answer, since elsewhere in BOL it says that DML and DDL do not overlap - BOL contradicts itself, so a question in this area is going to lead to discussion/debate about this and the discussion and debate are definitely worthwhile.

    However, if one had to separate DML and DDL so that no command can be both, my view would be that TRUNCAE TABLE is a DML statement. The argument that it isn't is usually based on the idea that it resets the current identity value to NULL if the table has an identity column. However, the same argument would make INSERT a DDL command when executed with IDENTITY INSERT off - such an insert alters exactly the same piece of metadata, the current identity value, so that argument clearly doesn't hold water. Neither does the argument that TRUNCATE is DDL because it takes a Sch-M lock - BOL says that some DML statements, including TRUNCATE, take that lock so it is clear that it is recognised that other DML commands may take Sch-M locks when the need to, in cases where MS has chosen to hold data about the current state of the table along with the schema metadata. Unlike the fixed identity seed the current identity value is not schema metadata, since it can be modified by data manipulation commands (INSERT) whereas the seed can only be modified by schema manipulation commands (DROP and recreate the table, or DROP and recreate the column using ALTER TABLE): despite the unfortunate terminology of a DBCC CHECKIDENT parameter that DBCC command has no effect on the schema metadata.

    Anyway, it's a good fun question that allows hoary old dinosaurs like to ramble on, so well done Naseer Ahmad. Keep on posing questions!

    Tom