How Truncate statement ?

  • Comments posted to this topic are about the item How Truncate statement ?

  • yikes... i chose error while truncating due to the fact the table wasn't named correctly in the truncate statement....

    :hehe:

  • Good Question. One more point can be added to the answer.

    You can truncate a table that has a foreign key that references itself

    See the link to the BOL below

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


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Nice Question : )

    Yes we can not use truncate on a table which is referenced by a FOREIGN KEY constraint

    The below statement is true if it is not referenced with any FOREIGN KEY:

    If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used.

  • Nice Question : )

    Yes we can not use truncate on a table which is referenced by a FOREIGN KEY constraint

    The below statement is true if it is not referenced with any FOREIGN KEY:

    If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used.

  • 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?

    Thanks

  • me too, although i know the logic behind truncate 😀

  • it truncates CustomerDocuments table

    but not CustomerMaster Error Message fk ref. fired while truncating CustomerMaster.

  • The error you will get is: CostomerMast does not Exist!!!

    The table is called CostomerMastER

  • w.rooks (10/21/2010)


    The error you will get is: CostomerMast does not Exist!!!

    The table is called CostomerMastER

    This was typo mistake.

    If you change it to CustomerMaster still SQL throws an error:

    Cannot truncate table 'CustomerMaster' because it is being referenced by a FOREIGN KEY constraint.

    Thanks

  • Good question, well done!

    as Kingston wrote earlier, per BOL looks like there are a few more restrictions on truncate,

    You cannot use TRUNCATE TABLE on tables that:

    * Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

    * Participate in an indexed view.

    * Are published by using transactional replication or merge replication.

    For tables with one or more of these characteristics, use the DELETE statement instead.

    TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL).

    With all these restrictions what kind of application would use truncate table?

    Thanks,

    Iulian

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Got it right as I knew that you could not truncate a table with FK Reference. However, the explanation for why it was the error is that the table does not exist. Not sure if this was a oversight on the person who entered the question or the person who published it. Good question though I think it will teach people some of the restrictions of truncate table.

  • Hardy21 (10/21/2010)


    w.rooks (10/21/2010)


    The error you will get is: CostomerMast does not Exist!!!

    The table is called CostomerMastER

    This was typo mistake.

    If you change it to CustomerMaster still SQL throws an error:

    Cannot truncate table 'CustomerMaster' because it is being referenced by a FOREIGN KEY constraint.

    You can't change the question! There is no way to know if a typo was ment to be made. My answer is the right one.

    Had the question been different your's would be right.

  • Got it right by process of elimination.

    First, I thought the answer might be 1. A new Identity would not have been generated after the truncate so it cannot be 1. Maybe it will be 21. No, the last identity generated for that table should be 20 -- not 21. This caused me to look closer at the query to notice the constraint.

Viewing 15 posts - 1 through 15 (of 33 total)

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