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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 1,057, Visits: 2,359
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: Thursday, October 16, 2014 2:45 PM
Points: 212, Visits: 233
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: Today @ 7:59 AM
Points: 579, Visits: 621
Interesting, good question.
Post #1436453
Posted Thursday, March 28, 2013 7:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 1,991, Visits: 1,496
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: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 18,064, Visits: 16,099
EZ PZ



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1436482
Posted Thursday, March 28, 2013 8:39 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: Tuesday, December 16, 2014 3:12 AM
Points: 800, Visits: 260
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 @ 10:47 AM
Points: 1,787, Visits: 3,435
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, November 21, 2014 11:01 AM
Points: 1,113, Visits: 1,089
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:04 PM
Points: 4,158, Visits: 5,555
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