Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

How Truncate statement ? Expand / Collapse
Author
Message
Posted Thursday, October 21, 2010 7:22 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:53 AM
Points: 1,407, Visits: 1,058

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
Post #1008454
Posted Thursday, October 21, 2010 7:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 5,369, Visits: 9,896
wware (10/21/2010)

Truncate does not fail for me...?

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

John
Post #1008455
Posted Thursday, October 21, 2010 7:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 6:53 AM
Points: 1,407, Visits: 1,058
Doh! Looks like I need to wake up a bit more, too. Careless cut and paste.
Post #1008465
Posted Thursday, October 21, 2010 7:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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.
Post #1008472
Posted Thursday, October 21, 2010 7:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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.
Post #1008481
Posted Thursday, October 21, 2010 8:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 2, 2014 6:42 AM
Points: 150, Visits: 320
nice question, thanks!

Answering these is becoming a daily habit for me now.
Post #1008517
Posted Thursday, October 21, 2010 8:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:38 AM
Points: 33,267, Visits: 15,436
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
Post #1008528
Posted Thursday, October 21, 2010 2:17 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 971, Visits: 929
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
Post #1008807
Posted Thursday, October 21, 2010 3:08 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,163, Visits: 2,191
Thanks for the question, I think it is good to help people understand the limitations of TRUNCATE TABLE.
Post #1008847
Posted Friday, October 22, 2010 6:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:57 AM
Points: 554, Visits: 863
Nice question :)
Post #1009125
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse