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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2101 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 Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: General Forum Members
Points: 81034 Visits: 17956
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

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

Group: General Forum Members
Points: 4664 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
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

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

Group: General Forum Members
Points: 190 Visits: 333
nice question, thanks!

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

Group: Administrators
Points: 332434 Visits: 20119
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
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4371 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
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

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

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