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»»»

Truncate Table Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 5:34 AM


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 @ 7:49 AM
Points: 850, Visits: 1,924
Awesome, actually really nice one.

(I had developed imaginary theory on this long back- what if the table is self joined with the key and would truncate work? - and I answered myself "probably yes - as it is a same table" - but never tried it practically, this was really good to see my imagination in the reality)


ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Post #1436404
Posted Thursday, March 28, 2013 7:07 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:14 AM
Points: 212, Visits: 224
AAhhhh!!!!!! This one hurts.. I didn't trust my instincts and on top was lazy to check...









___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic..
Post #1436449
Posted Thursday, March 28, 2013 7:14 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: Wednesday, July 16, 2014 6:57 AM
Points: 542, Visits: 569
Interesting, good question.
Post #1436453
Posted Thursday, March 28, 2013 7:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:34 AM
Points: 1,827, Visits: 1,361
Great question. Took a guess on this one and got it right. Definitely learned something today. Thanks!



Everything is awesome!
Post #1436464
Posted Thursday, March 28, 2013 7:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
Koen Verbeeck (3/28/2013)
Whoops, guessed the wrong one. Nice question, learned something.


+1 didn't realize this either... probably because in general the tables I want to truncate have additional foreign keys that depend on them :)

Still... good to know.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1436476
Posted Thursday, March 28, 2013 8:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 21,215, Visits: 14,918
EZ PZ



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1436482
Posted Thursday, March 28, 2013 8:39 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:04 AM
Points: 710, Visits: 228
I have to say that learning that a self-referencing Foreign Key doesn't prevent Truncate while interesting is probably not of that much practical value.

But I will say thanks for highlighting the very abbreviated form of Foreign Key definition is interesting and I didn't know that it could be reduced that much.
Post #1436508
Posted Thursday, March 28, 2013 9:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:41 AM
Points: 1,668, Visits: 3,346
good question. but for the life of me, i cannot come up with a reason to use this (nor anyone else where I work)- anybody?

jg
Post #1436547
Posted Thursday, March 28, 2013 11:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:28 PM
Points: 1,017, Visits: 1,002
BOL - Restrictions
You cannot use TRUNCATE TABLE on tables that:

•Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

Do we have any chance to use a table that has a foreign key that references itself?


--------------------------------------
"Stay Hungry, Stay Foolish." ― Jobs
“Everything has beauty, but not everyone sees it.” ― Confucius

Post #1436635
Posted Thursday, March 28, 2013 12:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 3,859, Visits: 5,001
chgn01 (3/28/2013)
BOL - Restrictions
You cannot use TRUNCATE TABLE on tables that:

•Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

Do we have any chance to use a table that has a foreign key that references itself?


Take a Staff table, for example.
some of the columns. may include, inter alia:
StaffId INT IDENTITY(1,1) NOT NULL,
SystemLogin CHAR(10) NOT NULL,
ManagerId INT CONSTRAINT FK_STAFF_MANAGER FOREIGN KEY REFERENCES Staff(StaffId)



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1436646
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse