SQL Clone
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.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1791 Visits: 1062
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
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34695 Visits: 16661
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.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1791 Visits: 1062
Doh! Looks like I need to wake up a bit more, too. Careless cut and paste.
SanDroid
SanDroid
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2416 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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2416 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 (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

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

Answering these is becoming a daily habit for me now.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)SSC Guru (146K reputation)

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

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2471 Visits: 1248
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
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4036 Visits: 2204
Thanks for the question, I think it is good to help people understand the limitations of TRUNCATE TABLE.
deepak.a
deepak.a
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1342 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