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 @ 1:05 PM
Points: 956, Visits: 2,177
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: Today @ 8:23 AM
Points: 212, Visits: 229
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: Thursday, August 21, 2014 2:58 PM
Points: 544, Visits: 574
Interesting, good question.
Post #1436453
Posted Thursday, March 28, 2013 7:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:53 AM
Points: 1,878, Visits: 1,412
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: Friday, September 12, 2014 8:38 AM
Points: 3,675, Visits: 72,434
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 @ 7:20 PM
Points: 21,751, Visits: 15,449
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
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 2:16 AM
Points: 738, Visits: 239
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: Today @ 11:43 AM
Points: 1,713, Visits: 3,379
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: Today @ 8:39 AM
Points: 1,074, Visits: 1,038
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 @ 9:44 AM
Points: 3,959, Visits: 5,194
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