SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Manoj Kumar / How Truncate statement ? / Latest Posts

RE: How Truncate statement ?

terrykzncs — Fri, 22 Apr 2011 12:53:38 GMT

So a DELETE would check if there were any rows still referencing the parent table, but a TRUNCATE can not and would therefore fail? even though we have cleared the child table already. Got to go home now, but will try it out soon. Thanks for the question. I always thought it would be easy to reset primary keys by doing a TRUNCATE on the tables.

RE: How Truncate statement ?

Dhruvesh Shah — Sun, 31 Oct 2010 20:27:19 GMT

Learned about Truncate when Foreign key ref. is available

RE: How Truncate statement ?

SQLRNNR — Tue, 26 Oct 2010 10:32:21 GMT

Thanks for the question.

RE: How Truncate statement ?

Koen Verbeeck — Fri, 22 Oct 2010 07:40:45 GMT

Nice question, learned something.

RE: How Truncate statement ?

deepak.a — Fri, 22 Oct 2010 06:32:57 GMT

Nice question :)

RE: How Truncate statement ?

UMG Developer — Thu, 21 Oct 2010 15:08:11 GMT

Thanks for the question, I think it is good to help people understand the limitations of TRUNCATE TABLE.

RE: How Truncate statement ?

Iulian -207023 — Thu, 21 Oct 2010 14:17:15 GMT

I understand: so it makes admin's life easier, I guess you can even schedule it to empty huge tables very easy and fast then load them with new and fresh dataand about applications this has to be handled carefuly since it needs db_ddladmin at the very least (quoteing John).Thanks a lot,Iulian

RE: How Truncate statement ?

Steve Jones - SSC Editor — Thu, 21 Oct 2010 08:26:01 GMT

The typo is corrected and I will award back points for those that might have mistakenly picked up on that.

RE: How Truncate statement ?

Stejones — Thu, 21 Oct 2010 08:18:32 GMT

nice question, thanks!Answering these is becoming a daily habit for me now.

RE: How Truncate statement ?

SanDroid — Thu, 21 Oct 2010 07:46:26 GMT

[quote][b]knmanojclt (10/21/2010)[/b][hr] I was searching in the sql for 'costomermaster' in the entire sql but I didn't find anything. Thanks & RegardsManoj Kumar[/quote]I really hope you are joking. If you are, then har har, you got us all by mispelling the QOTD and then making fun of our posts about your mistake. If you are not joking the section you 'missedspelling' is: [hr]TRUNCATE TABLE CustomerMastSELECT IDENT_CURRENT( 'CustomerMast' )[hr]Which completely changes the answer. There is no trucate error because the Truncate is not executed when there is no object found. The SELECT IDENT_CURRENT( 'CustomerMast' ) will always return NULL instead of 1 or 21 because there is no Identity records in the system tables for a table that does not exist.I hope you can find the Humorus in this myth stundering of your question.:cool:

RE: How Truncate statement ?

SanDroid — Thu, 21 Oct 2010 07:35:48 GMT

[quote][b]Iulian -207023 (10/21/2010)[/b][hr]With all these restrictions what kind of application would use truncate table?[/b][/quote]One that supports RDMS management and Administration.It can take up to 3 hours to transactionaly Delete and re-insert 3 million rows during a table schema update. That time is reduced to about 30 minutes if you clear the table using Truncate table. Most people that have to work with deploying production DB changes understand this, or learn about it quick. redgate has several utilities for RDMS management that understand this also.

RE: How Truncate statement ?

Rose Bud — Thu, 21 Oct 2010 07:29:20 GMT

Doh! Looks like I need to wake up a bit more, too. Careless cut and paste.

RE: How Truncate statement ?

John Mitchell-245523 — Thu, 21 Oct 2010 07:25:20 GMT

[quote][b]wware (10/21/2010)[/b][hr][b]Truncate does not fail for me...?[/b][/quote]That's because you haven't defined a foreign key constraint.John

RE: How Truncate statement ?

Rose Bud — Thu, 21 Oct 2010 07:22:31 GMT

[b]Truncate does not fail for me...?[/b]CREATE TABLE [dbo].[CustomerMaster]([CustomerId] [int] IDENTITY(1,1) NOT NULL,[CustomerCode] [varchar](30),[CustomerName] [varchar](200),[CreatedDate] [datetime],[ContactNo] [varchar](20) CONSTRAINT [PK_CustomerId] PRIMARY KEY CLUSTERED ([CustomerId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [dbo].[CustomerDocuments]([DocAttachmentId] [int] IDENTITY(1,1) NOT NULL,[CustomerId] [int] NOT NULL,[DocumentType] [varchar](5),[DocumentName] [varchar](200) NULL,CONSTRAINT [PK_CustomerDocs] PRIMARY KEY CLUSTERED ([DocAttachmentId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]insert into [CustomerMaster] (CustomerCode, customerName, CreatedDate, ContactNo) values ('ABC','ABC','1/1/10','0000000') insert into CustomerDocuments (CustomerID, DocumentType, DocumentName) values ('1','123','123')insert into CustomerDocuments (CustomerID, DocumentType, DocumentName) values ('1','456','456')select * from CustomerDocumentsselect * from CustomerMasterTRUNCATE TABLE CustomerDocumentsTRUNCATE TABLE CustomerMasterSELECT IDENT_CURRENT( 'CustomerMaster' )drop table CustomerDocumentsdrop table CustomerMaster

RE: How Truncate statement ?

Daniel Bowlin — Thu, 21 Oct 2010 06:53:43 GMT

I have to make a point of waking up a little more before answering these questions. :angry:

RE: How Truncate statement ?

Imran Ashraf-452633 — Thu, 21 Oct 2010 05:51:15 GMT

The misspelling is TRUNCATE TABLE CustomerMastThe table is called CustomerMaster there is no reference to the table you are looking for in the SQL. This was yet again a misspelling on the thread by one of the contributors.

RE: How Truncate statement ?

Manoj KN — Thu, 21 Oct 2010 05:04:30 GMT

Hi , I was searching in the sql for 'costomermaster' in the entire sql but I didn't find anything. It would be grate If u can tell me the statement misspelled. Thanks & RegardsManoj Kumar

RE: How Truncate statement ?

Manoj KN — Thu, 21 Oct 2010 04:38:21 GMT

Hi, Truncate statement is faster than than delete statement and it can be extensively use while working with temporary tables. Also it is useful to reset the identity column value that delete statement does not. Thanks & RegardsManoj Kumar.

RE: How Truncate statement ?

John Mitchell-245523 — Thu, 21 Oct 2010 03:07:16 GMT

[quote][b]Hugo Kornelis (10/21/2010)[/b][hr][quote][b]Iulian -207023 (10/21/2010)[/b][hr][b]With all these restrictions what kind of application would use truncate table?[/b][/quote]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.[/quote]It should be noted that TRUNCATE is a DDL operation, not DML, and as such requires membership of db_ddladmin at the very least. You would want to weight up the benefits of any performance gains against the security costs of granting such access to users or application service accounts.John

RE: How Truncate statement ?

cengland0 — Thu, 21 Oct 2010 03:01:54 GMT

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.

RE: How Truncate statement ?

William Rooks — Thu, 21 Oct 2010 02:02:28 GMT

[quote][b]Hardy21 (10/21/2010)[/b][hr][quote][b]w.rooks (10/21/2010)[/b][hr]The error you will get is: CostomerMast does not Exist!!!The table is called CostomerMastER[/quote]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.[/quote]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.

RE: How Truncate statement ?

Imran Ashraf-452633 — Thu, 21 Oct 2010 01:57:29 GMT

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.

RE: How Truncate statement ?

Hugo Kornelis — Thu, 21 Oct 2010 01:57:18 GMT

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?[quote][b]Hardy21 (10/20/2010)[/b][hr]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?[/quote]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.[quote][b]Iulian -207023 (10/21/2010)[/b][hr][b]With all these restrictions what kind of application would use truncate table?[/b][/quote]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.

RE: How Truncate statement ?

Iulian -207023 — Thu, 21 Oct 2010 01:34:21 GMT

Good question, well done!as Kingston wrote earlier, per [url=http://msdn.microsoft.com/en-us/library/ms177570.aspx][u]BOL[/u][/url] looks like there are a few more restrictions on truncate, [quote]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).[/quote][b]With all these restrictions what kind of application would use truncate table?[/b]Thanks, Iulian

RE: How Truncate statement ?

Hardy21 — Thu, 21 Oct 2010 00:40:28 GMT

[quote][b]w.rooks (10/21/2010)[/b][hr]The error you will get is: CostomerMast does not Exist!!!The table is called CostomerMastER[/quote]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.

RE: How Truncate statement ?

William Rooks — Thu, 21 Oct 2010 00:38:13 GMT

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

RE: How Truncate statement ?

pravin wagh — Thu, 21 Oct 2010 00:38:04 GMT

it truncates CustomerDocuments tablebut not CustomerMaster Error Message fk ref. fired while truncating CustomerMaster.

RE: How Truncate statement ?

jshailendra — Thu, 21 Oct 2010 00:37:18 GMT

me too, although i know the logic behind truncate :-D

RE: How Truncate statement ?

Hardy21 — Wed, 20 Oct 2010 23:51:17 GMT

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?

RE: How Truncate statement ?

sharath.chalamgari — Wed, 20 Oct 2010 23:27:15 GMT

Nice Question : ) Yes we can not use truncate on a table which is referenced by a FOREIGN KEY constraintThe 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.

RE: How Truncate statement ?

sharath.chalamgari — Wed, 20 Oct 2010 23:25:52 GMT

RE: How Truncate statement ?

Kingston Dhasian — Wed, 20 Oct 2010 22:58:51 GMT

Good Question. One more point can be added to the answer. [b][i]You can truncate a table that has a foreign key that references itself [/i][/b]See the link to the BOL below[url]http://msdn.microsoft.com/en-us/library/ms177570.aspx[/url]

RE: How Truncate statement ?

lukus_g — Wed, 20 Oct 2010 22:11:23 GMT

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

How Truncate statement ?

Manoj KN — Wed, 20 Oct 2010 21:05:59 GMT

Comments posted to this topic are about the item [B]How Truncate statement ?[/B]