|
|
Posted by Erland Sommarskog on 08/06/05 14:55
(csomberg@dwr.com) writes:
> SQL Server 2000 SP4
>
> I was wondering what has the best performance for maintaining
> referential integrity ... triggers, constraints etc .....
An AFTER trigger is almost bound to give worse performance, because by
the time when the trigger is entered, the data is already there in the
table, thus an error causes a rollback.
An INSTEAD OF trigger could hypothetically be faster when something
goes wrong, because nothing has happned yet.
Then again, triggers offers some good performance traps - the tables
"inserted" and "deleted" are constructed from the transaction log
and access to them can be slow. If you are to make several accesses to
these tables in the trigger, it is often better to to insert the
data into table variables instead.
So I would say that constraints in the normal case is faster than triggers.
In any case, you need to master both. As Hugo says, constraints are so
much simpler to implement, that that is reason alone to use them whenever
possible.
On the other hand, far from everthing can be handled in constraints, so
triggers are more general.
So are there cases when triggers beats constraints? Yes. Say that you
have an CustomerCategories table that has an IsActive flag. An active
customer must belong to a customer category which also is active. This
can be implemented with a constraint, if you use a UDF to check the
status of the customer category. Luckily, most people wouldn't think
of this, and use a trigger instead. I played with this on a table with
30000 rows. An update of all rows went from one second to thirty...
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|