How Truncate statement ?

Question

Post reply

How Truncate statement ?

Manoj KN

SSC Journeyman

Points: 80
More actions
October 20, 2010 at 9:06 pm

#230111

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

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

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

lukus_g SSC Eights! Points: 958 More actions · Answer 1

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

:hehe:

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

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/

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 3

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.

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 4

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.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 5

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

jshailendra SSCrazy Points: 2260 More actions · Answer 6

me too, although i know the logic behind truncate 😀

pravin wagh SSC Veteran Points: 223 More actions · Answer 7

it truncates CustomerDocuments table

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

William Rooks SSC Rookie Points: 29 More actions · Answer 8

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

The table is called CostomerMastER

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 9

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

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 10

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 11

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/

Imran Ashraf-452633 SSCommitted Points: 1969 More actions · Answer 12

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.

William Rooks SSC Rookie Points: 29 More actions · Answer 13

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.

cengland0 SSCertifiable Points: 6102 More actions · Answer 14

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.