|
Home > Archive > MS SQL Server > December 2006 > RI Question: Declarative vs Trigger
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
RI Question: Declarative vs Trigger
|
|
| Isaac Alexander 2006-12-12, 7:12 pm |
| 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.
| |
| Alex Kuznetsov 2006-12-12, 7:12 pm |
|
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/
| |
| Isaac Alexander 2006-12-12, 7:12 pm |
|
"Alex Kuznetsov" < AK_TIREDOFSPAM@hotma
il.COM> wrote in message
news:1165947132.769112.294310@f1g2000cwa.googlegroups.com...
>
> Isaac Alexander wrote:
>
> 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.
|
|
|
|
|