Nice question, and a (to me) surprising large number opf incorrect answers. Shame about the typo in the table name, though - it was obvious that this was not intended, but it will confuse some people. And seriously, how much time would it have cost you to copy and paste the code in a query window and hit execute before submitting the question?
Hardy21 (10/20/2010)
Nice question.Delete is working for CustomerMaster table but truncate throws an error. I would like to know why it is not working? Any specific reason?
DELETE works on a row-by-row basis. Even if no WHERE clause is used. For each row deleted, the refential integrity can be checked.
TRUNCATE TABLE works by deallocating entire pages in the database file, without looking at the contents. There is no way to check if rows deleted were referenced by a FOREIGN KEY. Therefor, TRUNCATE TABLE is simply forbidden if there is a FOREIGN KEY that references the target table.
Iulian -207023 (10/21/2010)
With all these restrictions what kind of application would use truncate table?
An application that does not use triggers, is not involved in transactional replication or merge replication, and that is able to temporarily remove referencing foreign key constraints and indexed views based on the table in order to profit from a tremendous performance gain when a very large table has to be emptied.