Triiger -vs- Constraint

[csomberg@dwr.com]

SQL Server 2000 SP4

I was wondering what has the best performance for maintaining
referential integrity ... triggers, constraints etc .....

Thanks,

Craig

[Hugo Kornelis]

Re: Triiger -vs- Constraint

On 5 Aug 2005 16:17:51 -0700, csomberg@dwr.co m wrote:
[color=blue]
>SQL Server 2000 SP4
>
>I was wondering what has the best performance for maintaining
>referential integrity ... triggers, constraints etc .....
>
>Thanks,
>
>Craig[/color]

Hi Craig,

Use constraints where you can. Resort to triggers only when you can't
use constraints. Constraints are faster, plus they provide extra
information to the optimzer. And they're less work too :-)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Re: Triiger -vs- Constraint

(csomberg@dwr.c om) writes:[color=blue]
> SQL Server 2000 SP4
>
> I was wondering what has the best performance for maintaining
> referential integrity ... triggers, constraints etc .....[/color]

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 CustomerCategor ies 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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Martijn Tonies]

Re: Triiger -vs- Constraint

> On the other hand, far from everthing can be handled in constraints, so[color=blue]
> triggers are more general.
>
>
> So are there cases when triggers beats constraints? Yes. Say that you
> have an CustomerCategor ies 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...[/color]

Flags are bad :-)


--
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions

http://www.upscene.com

Database development questions? Check the forum!

http://www.databasedevelopmentforum.com

[Hugo Kornelis]

Re: Triiger -vs- Constraint

On Sat, 6 Aug 2005 11:55:40 +0000 (UTC), Erland Sommarskog wrote:
[color=blue]
>So are there cases when triggers beats constraints? Yes. Say that you
>have an CustomerCategor ies 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.[/color]

Hi Erland,

Am I missing something from your description? Unless I do, the best wat
to do this is to add a (seemingly redundant) UNIQUE constraint, then use
a standard FOREIGN KEY constraint:

CREATE TABLE Categories
(CategoryID int NOT NULL,
IsActive char(1) DEFAULT 'Y',
-- other columns,
PRIMARY KEY (CategoryID),
CHECK (IsActive = 'Y' OR IsActive IS NULL),
UNIQUE (CategoryID, IsActive),
)

CREATE TABLE Customers
(CustomerID int NOT NULL,
IsActive char(1) DEFAULT 'Y',
CategoryID int NOT NULL,
-- other columns,
PRIMARY KEY (CustomerID),
CHECK (IsActive = 'Y' OR IsActive IS NULL),
FOREIGN KEY (CategoryID, IsActive)
REFERENCES Categories (CategoryID, IsActive),
)

Okay, I agree that it's somewhat hackish - having IsActive either 'Y' or
NULL is far from intuitive. In real life, I'd probably go for the
trigger as well :-)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Re: Triiger -vs- Constraint

Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=blue]
> Am I missing something from your description? Unless I do, the best wat
> to do this is to add a (seemingly redundant) UNIQUE constraint, then use
> a standard FOREIGN KEY constraint:[/color]

Certainly an interesting slant to it, but that use of NULL is making
me nervous. And I don't like using char(1) columns for flags. So what
was the value for is active now again? Was that Y, J, T or something
else? (Which matters when you say things like "WHERE IsActive = 'J'".)
A bit column is better, but it's weird to have a bit column that only may
be 1.

And in our case, we don't have a flag here, but a datetime column to
tell you when the entity was deregistered. (And, since our system has
a history since 1992 we are still on ANSI_NULLS off, and cannot index
computed columns.)

What I have been contemplating for this particlar case, is to have
tables like ActiveCustomers , ActiveCategorie s etc.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: Triiger -vs- Constraint

On Sat, 6 Aug 2005 16:49:04 +0000 (UTC), Erland Sommarskog wrote:
[color=blue]
>Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=green]
>> Am I missing something from your description? Unless I do, the best wat
>> to do this is to add a (seemingly redundant) UNIQUE constraint, then use
>> a standard FOREIGN KEY constraint:[/color]
>
>Certainly an interesting slant to it, but that use of NULL is making
>me nervous. And I don't like using char(1) columns for flags. So what
>was the value for is active now again? Was that Y, J, T or something
>else? (Which matters when you say things like "WHERE IsActive = 'J'".)
>A bit column is better, but it's weird to have a bit column that only may
>be 1.[/color]

Hi Erland,

char(1), tinyint, or bit - they all will do. In cases like this, with
only one value or NULL permitted, I'd only use IS NULL / IS NOT NULL in
the WHERE clause. It's a lot trickier when two values are allowed - do I
test for Y/N, J/N, T/F or W/O? Switching to tinyint or even bit won't
help much - in the case of bit, I know that I test for 1 or 0, but I'll
still have to lookup if I chose 1 to mean yes or no.

[color=blue]
>And in our case, we don't have a flag here, but a datetime column to
>tell you when the entity was deregistered.[/color]

Spoilsport! <g>
[color=blue]
>(And, since our system has
>a history since 1992 we are still on ANSI_NULLS off, and cannot index
>computed columns.)[/color]

I don't think that matters much. With ANSI_NULLS on, you can index the
computed column, but you still can't use it in a FOREIGH KEY constraint.

BTW, I can't recall ever seeing you post a message here that relies on
non-ANSI null behaviour - I think I'd make that mistake every once in a
while if I had to switch between the two each day!

[color=blue]
>What I have been contemplating for this particlar case, is to have
>tables like ActiveCustomers , ActiveCategorie s etc.[/color]

Too bad you can't decalre a foreign key constraint between two indexed
views - that would let you have the best of both worlds!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Re: Triiger -vs- Constraint

Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=blue]
> I don't think that matters much. With ANSI_NULLS on, you can index the
> computed column, but you still can't use it in a FOREIGH KEY constraint.[/color]

In SQL 2005 you can:

CREATE TABLE a (a int NOT NULL,
b AS a + 3 PERSISTED)
CREATE TABLE c (c int NOT NULL PRIMARY KEY)
go
ALTER TABLE a ADD CONSTRAINT fk
FOREIGN KEY (b) REFERENCES c(c)
go
INSERT c (c) VALUES (9)
INSERT a (a) VALUES (6)
go
DROP TABLE a, c
[color=blue]
> BTW, I can't recall ever seeing you post a message here that relies on
> non-ANSI null behaviour - I think I'd make that mistake every once in a
> while if I had to switch between the two each day![/color]

Of course, most of the time we code as if we had ANSI_NULLS on. Our
load tool screams if it sees things like "IF @a = NULL". But then there
are some misdesigns which relies on "WHERE col = @col" to work, even
if col and @col are NULL. And this is an spot where a redesign would
have very far-reaching consequences. (And adding "col IS NULL AND @col IS
NULL" could wreck use of indexes.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: Triiger -vs- Constraint

On Sat, 6 Aug 2005 21:37:46 +0000 (UTC), Erland Sommarskog wrote:
[color=blue]
>Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=green]
>> I don't think that matters much. With ANSI_NULLS on, you can index the
>> computed column, but you still can't use it in a FOREIGH KEY constraint.[/color]
>
>In SQL 2005 you can:
>
> CREATE TABLE a (a int NOT NULL,
> b AS a + 3 PERSISTED)
> CREATE TABLE c (c int NOT NULL PRIMARY KEY)
> go
> ALTER TABLE a ADD CONSTRAINT fk
> FOREIGN KEY (b) REFERENCES c(c)
> go
> INSERT c (c) VALUES (9)
> INSERT a (a) VALUES (6)
> go
> DROP TABLE a, c[/color]

Hi Erland,

Thanks. I can't wait until SQL Server 2005 is released, and I can
finally get to play around with all the nice new features!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)