Cascade Update and Delete Related Fields..

I am building tables using the database diagram with enterprise manager. One of the options available is to "Cascade Update and Delete Related Fields". I think this means that if I change a PK Identifier in a table all related FK's will be updated to the new value and if I delete the PK all related FK records will be deleted. Is this correct? Is this a good idea to do something like this?

I have always handled what needs to be changed using SP's following business rules. A friend has been preaching the use of the "Cascade Update and Delete Related Fields" functionality provided with the database.

Please let me know what you think.

ThanksAs you already mention, I think this is a matter of opinion. The advantage for cascading is that you don't have to do any checking and programming to avoid data inconstency. Disadvantage is that you don't have any control about the updates/deletes of the related records. For the manual programming through sp's it's offcourse the other way around.
In other words: you have to see in every situation what's the best solution.

Cascade deletes in SQL Server 7.0?

Is it possible to have data deleted(automaticly) from tables related to the one im deleting from..

Example:

Table A have a one to many relation to 6 other tables.

I will delete a member from Table A, will the data conserning the member in the other 6 tables be automaticly removed?

Best wishes.

(If not, how should I write the sp to handle this task?)For SQL Server 7.0 you would have to implement TRIGGERS to perform cascade deletes. However in SQL Server 2000 there is cascade deletes, defined by 'Cascading Referential Integrity Constraints'|||Okay!

Thanks for responding - Scre@.m

Cascade delete relationship slower?

I have several related tables that have the cascade delete option selected i
n
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things dow
n?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?Do you have indexes on the FK's in each of the child tables?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
I have several related tables that have the cascade delete option selected
in
their relationships. There is one parent table that all the child tables
relate to (sometimes many layers deep). In order to allow deleting of a
parent record I have created a stored procedure that goes through the
hierarchy and deletes the related records in the child tables in the correct
order as to not break referential integrity.
My problem is that this stored procedure is incredibly slow. This seems
strange because for each row you delete in the parent, only a handful of
child rows have to be deleted.
1) Is it possible that the cascade delete option could be slowing things
down?
2) Since I am taking care of the cascading of deletes on my own in the
stored proc, should I remove that option from the relationships?
3) What other factors could be contributing to very slow delete performance?|||Could you post the DDL for the procedure?
Wild guess: the performance problem is a result of locking. How quick is a
delete from the parent table using just the CASCADEs?
--
"Dan" wrote:

> I have several related tables that have the cascade delete option selected
in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the corre
ct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things d
own?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete performanc
e?
>
>|||
> 1) Is it possible that the cascade delete option could be slowing things
> down?
Cascade delete option? If you are using that option then this deletion
should just magically occur, whap bam boom. No need for a stored procedure.
I don't believe it will make any difference, as long as there are no rows to
delete. At this point the check for rows to cause NO ACTION will be the
exact same as the check for rows to CASCADE the action to. To optimize it,
think of the select statement you would run to see if there were rows:
select *
from childTable
where key in (parentTableKeyValue1, parentTableKeyValue1, ...,
parentTableKeyValueN)
As Tom mentions, you will probably need to index the columns in the foreign
keys, for this reason (I am just feeling wordy tonight!) Of course, if you
are deleting large number of rows at a time, then indexes might not help.

> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
Well, I don't know if it is hurting performance, but it certainly isn't a
good use of your time to do it both ways. If cascade deletes won't work for
you, then yes, you should remove it.

> 3) What other factors could be contributing to very slow delete
> performance?
Poor disk subsystem, not enough ram, too many concurrent users, gremlins,
etc. Seriously this is a big question. You need to optimize the queries
and look for table scans or cluster index scans (ordered table scans) to
determine if you are getting good plans. Then hit perfmon and see if the
system is performing.
I think that indexing the foreign keys is probably the correct answer, but
it is a very open question.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:6162CFA5-5032-4F82-86E2-092B3B76F1EE@.microsoft.com...
>I have several related tables that have the cascade delete option selected
>in
> their relationships. There is one parent table that all the child tables
> relate to (sometimes many layers deep). In order to allow deleting of a
> parent record I have created a stored procedure that goes through the
> hierarchy and deletes the related records in the child tables in the
> correct
> order as to not break referential integrity.
> My problem is that this stored procedure is incredibly slow. This seems
> strange because for each row you delete in the parent, only a handful of
> child rows have to be deleted.
> 1) Is it possible that the cascade delete option could be slowing things
> down?
> 2) Since I am taking care of the cascading of deletes on my own in the
> stored proc, should I remove that option from the relationships?
> 3) What other factors could be contributing to very slow delete
> performance?
>
>

Capture PERFMON counters on fly

Could anyone help to capture perfmon counters including SQL Server related to
a table on fly. I'm aware of DBCC PERFMON but it doesn't give all the
counters that are related to SQL SErver.
Thanks in advance.
Check out master..sysperfinfo. Note though that the average values are not timeadjusted in this
table. Search KB and you will find an article on this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Satya SKJ" <satyaskj@.yahoo.co.uk> wrote in message
news:3433A607-BE42-4B8D-B99B-7CE406795C37@.microsoft.com...
> Could anyone help to capture perfmon counters including SQL Server related to
> a table on fly. I'm aware of DBCC PERFMON but it doesn't give all the
> counters that are related to SQL SErver.
> Thanks in advance.