Re: Strange behavior with identity columns...any advice?
From: Itzik Ben-Gan (itzik_at_REMOVETHIS.SolidQualityLearning.com)
Date: 10/17/04
- Next message: Nenad: "Re: Error in this sp whith SP_EXECUTESQL"
- Previous message: Uri Dimant: "Re: Joins"
- In reply to: Joe Celko: "Re: Strange behavior with identity columns...any advice?"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 17 Oct 2004 11:09:42 +0200
> I have to disagree. TRUNCATE does not work on referenced tables and it
> does not fire TRIGGERs. If I am using FK or TRIGGERs for some of my
> data integrity, I can be screwed by this vendor extension.
That's true.
Regarding referencing tables, you cannot use DELETE either to clear a table
if there are referencing rows, unless you're cascading or clearing the
referencing tables first. If we're talking about clearing both referencing
and referenced tables, you can drop the FKs, truncate, then create them back
in the same transaction.
As for triggers, you do need to explicitly substitute their intended
activity.
Usually, because TRUNCATE is so much faster, it's worthwhile.
I do agree that the DBCC CHECKIDENT inconsistency generates confusion here,
and complicates things.
> As I recall all it did in early versions of T-SQL was move an EOF
> pointer to the start of the physical storage for a table to wipe out the
> contigous file stucture. That made it very fast, but thre were no
> safety checks. Has it gotten smarter under the covers?
TRUNCATE simply deallocates extents, and logs only the extent addresses in
the transaction log (for rollback purposes), while a DELETE is fully logged.
That's the reason for the performance difference.
What do you mean by no safety checks?
-- BG, SQL Server MVP www.SolidQualityLearning.com "Joe Celko" <jcelko212@earthlink.net> wrote in message news:OE7$pg9sEHA.3556@TK2MSFTNGP10.phx.gbl... >>> Since TRUNCATE is the way to go when you want to empty a table, by > using it exclusively, you can ensure consistency. << > > I have to disagree. TRUNCATE does not work on referenced tables and it > does not fire TRIGGERs. If I am using FK or TRIGGERs for some of my > data integrity, I can be screwed by this vendor extension. > > As I recall all it did in early versions of T-SQL was move an EOF > pointer to the start of the physical storage for a table to wipe out the > contigous file stucture. That made it very fast, but thre were no > safety checks. Has it gotten smarter under the covers? > > --CELKO-- > Please post DDL, so that people do not have to guess what the keys, > constraints, Declarative Referential Integrity, datatypes, etc. in your > schema are. Sample data is also a good idea, along with clear > specifications. > > > *** Sent via Developersdex http://www.developersdex.com *** > Don't just participate in USENET...get rewarded for it!
- Next message: Nenad: "Re: Error in this sp whith SP_EXECUTESQL"
- Previous message: Uri Dimant: "Re: Joins"
- In reply to: Joe Celko: "Re: Strange behavior with identity columns...any advice?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
