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


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: Friday, April 18, 2014 10:45 AM
Points: 673, Visits: 1,551
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
--
There are only 10 types of people in the world, those who understand binary, and those who don't.

Note: (as of now) only.. 1 and 4 applies (i am on my way...)
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: Tuesday, March 11, 2014 8:44 AM
Points: 212, Visits: 217
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, April 16, 2014 6:57 AM
Points: 515, Visits: 547
Interesting, good question.
Post #1436453
Posted Thursday, March 28, 2013 7:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:24 AM
Points: 1,718, Visits: 1,248
Great question. Took a guess on this one and got it right. Definitely learned something today. Thanks!



Yeah, uh huh, you know what it is. Everything I do, I do it big

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: Tuesday, April 15, 2014 12:05 PM
Points: 3,569, Visits: 72,411
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: Yesterday @ 9:30 PM
Points: 20,467, Visits: 14,104
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: Monday, April 14, 2014 4:41 AM
Points: 690, Visits: 218
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, April 18, 2014 8:48 AM
Points: 1,593, Visits: 3,259
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


SSC Eights!

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

Group: General Forum Members
Last Login: 2 days ago @ 2:02 PM
Points: 980, Visits: 972
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: Friday, April 18, 2014 6:40 AM
Points: 3,683, Visits: 4,818
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