SQL Server 2000 SP4
I was wondering what has the best performance for maintaining
referential integrity ... triggers, constraints etc .....
Thanks,
Craig 8 1927
On 5 Aug 2005 16:17:51 -0700, cs******@dwr.com wrote: SQL Server 2000 SP4
I was wondering what has the best performance for maintaining referential integrity ... triggers, constraints etc .....
Thanks,
Craig
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)
(cs******@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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
> 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...
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
On Sat, 6 Aug 2005 11:55:40 +0000 (UTC), Erland Sommarskog wrote: 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.
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)
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes: 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:
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, ActiveCategories etc.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
On Sat, 6 Aug 2005 16:49:04 +0000 (UTC), Erland Sommarskog wrote: Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes: 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: 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.
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.
And in our case, we don't have a flag here, but a datetime column to tell you when the entity was deregistered.
Spoilsport! <g>
(And, since our system has a history since 1992 we are still on ANSI_NULLS off, and cannot index computed columns.)
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!
What I have been contemplating for this particlar case, is to have tables like ActiveCustomers, ActiveCategories etc.
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)
Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes: 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.
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
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!
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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
On Sat, 6 Aug 2005 21:37:46 +0000 (UTC), Erland Sommarskog wrote: Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes: 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.
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
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) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Kamil |
last post by:
Hello
What should I use for better perfomance since
unique constraint always use index ?
Thanks
Kamil
|
by: cody |
last post by:
I've seen an Introduction on ADO.NET with its Datasets on .NET TV and Iam
now wondering how it is realized/used in real world applications.
I don't believe that one would create a dataset and add...
|
by: Sunny |
last post by:
Hi,
I'm using VS.net 2003 and C#.
I created (using buildin designer) a dataset with 2 tables.
TableOne has 2 fields: sName and sTable (both string) and a primary key
TestKey, set on sName....
|
by: Thomas LeBlanc |
last post by:
Does a Unique Constraint build a unique index?
What is the difference?
Thanks,
Thomas LeBlanc
_________________________________________________________________
Get a FREE computer virus...
|
by: Ron Johnson |
last post by:
http://hardware.devchannel.org/hardwarechannel/03/10/20/1953249.shtml?tid=20&tid=38&tid=49
--
-----------------------------------------------------------------
Ron Johnson, Jr....
|
by: Brian Keating |
last post by:
Hi there,
Is it possible to add a unique constraint on two columns in a table, so that
the constraint is a composite of the two? i.e. these two columns together
should be unique...?
i.e....
|
by: Dan Holmes |
last post by:
I have a class that i need a constraint of int, string, float or bool. I
have tried the following but can't make VS accept it. I read the docs
and they showed that any value type can be used...
|
by: wizofaus |
last post by:
I have a chunk of code which is essentially
IDbCommand cmd = db.CreateCommand();
cmd.CommandText = "SELECT X, Y, Count(*) FROM Foo WHERE Z = 1 GROUP BY
X, Y";
using (IDataReader reader =...
|
by: =?Utf-8?B?VGFz?= |
last post by:
Hi, first of all, I'm using:
Microsoft Visual Studio 2005
Version 8.0.50727.762 (SP.050727-7600)
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: C# Express.
I have added a...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |