RI Question: Declarative vs Trigger

I am using SQL Server 7, 2000 and 2005.
All of my cascading RI is handled via triggers (generated by data modeller).
I only use triggers for RI and they implement simple cascading or setting
null.
From a performance point of view (100 million plus recs in a table), is it
better to use declarative RI or trigger RI?
The reason I ask is because I currently still support SQL 7 and I would like
to avoid separate app versions of the database per SQL Server version. But
if performance is better, I will consider separate app db versions.Isaac Alexander wrote:
> I am using SQL Server 7, 2000 and 2005.
> All of my cascading RI is handled via triggers (generated by data modeller).
> I only use triggers for RI and they implement simple cascading or setting
> null.
> From a performance point of view (100 million plus recs in a table), is it
> better to use declarative RI or trigger RI?
> The reason I ask is because I currently still support SQL 7 and I would like
> to avoid separate app versions of the database per SQL Server version. But
> if performance is better, I will consider separate app db versions.
Isaac,
Usually RI performs much better than triggers. Also be aware that in
some cases triggers just do not fire, for instance
- TRUNCATE TABLE does not fire delete triggers
- nested and recursive triggers settings may cause triggers to not
fire
- triggers that work under READ COMMITTED isolation level may silently
fail under READ COMMITTED SNAPSHOT
So triggers are not 100% watertight.
On top of that, the optimizer may use RI as additional information and
come up with a better plan - it does not look into triggers.
--
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||"Alex Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1165947132.769112.294310@.f1g2000cwa.googlegroups.com...
> Isaac Alexander wrote:
>> I am using SQL Server 7, 2000 and 2005.
>> All of my cascading RI is handled via triggers (generated by data
>> modeller).
>> I only use triggers for RI and they implement simple cascading or setting
>> null.
>> From a performance point of view (100 million plus recs in a table), is
>> it
>> better to use declarative RI or trigger RI?
>> The reason I ask is because I currently still support SQL 7 and I would
>> like
>> to avoid separate app versions of the database per SQL Server version.
>> But
>> if performance is better, I will consider separate app db versions.
> Isaac,
> Usually RI performs much better than triggers. Also be aware that in
> some cases triggers just do not fire, for instance
> - TRUNCATE TABLE does not fire delete triggers
> - nested and recursive triggers settings may cause triggers to not
> fire
> - triggers that work under READ COMMITTED isolation level may silently
> fail under READ COMMITTED SNAPSHOT
> So triggers are not 100% watertight.
> On top of that, the optimizer may use RI as additional information and
> come up with a better plan - it does not look into triggers.
>
Thanks. I was hoping that I got this answer.