How Truncate statement ?

Question

How Truncate statement ?

Viewing 15 posts - 16 through 30 (of 33 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Hugo Kornelis (10/21/2010)
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.

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

Manoj KN SSC Journeyman Points: 80 More actions · Answer 2

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 & Regards

Manoj Kumar.

Manoj KN SSC Journeyman Points: 80 More actions · Answer 3

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 & Regards

Manoj Kumar

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

The misspelling is

TRUNCATE TABLE CustomerMast

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 5

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

Rose Bud Hall of Fame Points: 3147 More actions · Answer 6

Truncate does not fail for me...?

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]

GO

CREATE 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 CustomerDocuments

select * from CustomerMaster

TRUNCATE TABLE CustomerDocuments

TRUNCATE TABLE CustomerMaster

SELECT IDENT_CURRENT( 'CustomerMaster' )

drop table CustomerDocuments

drop table CustomerMaster

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

wware (10/21/2010)
Truncate does not fail for me...?

That's because you haven't defined a foreign key constraint.

John

Rose Bud Hall of Fame Points: 3147 More actions · Answer 8

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

SanDroid SSChampion Points: 10068 More actions · Answer 9

Iulian -207023 (10/21/2010)
With all these restrictions what kind of application would use truncate table?[/b]

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.

SanDroid SSChampion Points: 10068 More actions · Answer 10

knmanojclt (10/21/2010)
I was searching in the sql for 'costomermaster' in the entire sql but I didn't find anything.
Thanks & Regards
Manoj Kumar

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:

TRUNCATE TABLE CustomerMast

SELECT IDENT_CURRENT( 'CustomerMast' )

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:

Stejones SSC Veteran Points: 260 More actions · Answer 11

nice question, thanks!

Answering these is becoming a daily habit for me now.

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 12

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

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

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 data

and about applications this has to be handled carefuly since it needs db_ddladmin at the very least (quoteing John).

Thanks a lot,

Iulian

UMG Developer SSChampion Points: 13482 More actions · Answer 14

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

deepak.a SSCertifiable Points: 5330 More actions · Answer 15

deepak.a

SSCertifiable

Points: 5330

October 22, 2010 at 6:32 am

#1240853

Nice question 🙂