Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How Truncate statement ?


How Truncate statement ?

Author
Message
Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 1061
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
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7353 Visits: 15068
wware (10/21/2010)

Truncate does not fail for me...?

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

John
Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1573 Visits: 1061
Doh! Looks like I need to wake up a bit more, too. Careless cut and paste.
SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1410 Visits: 1046
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
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1410 Visits: 1046
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
Stejones
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 323
nice question, thanks!

Answering these is becoming a daily habit for me now.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35873 Visits: 18715
The typo is corrected and I will award back points for those that might have mistakenly picked up on that.

Follow me on Twitter: @way0utwestForum Etiquette: How to post data/code on a forum to get the best help
Iulian -207023
Iulian -207023
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1196 Visits: 1226
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
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
Thanks for the question, I think it is good to help people understand the limitations of TRUNCATE TABLE.
deepak.a
deepak.a
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 863
Nice question Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search