April 2, 2009 at 11:01 pm
Hi
What is the maximum number of tables that can be affected in a single INSERT / DELETE/UPDATE statement?
April 2, 2009 at 11:08 pm
one
April 3, 2009 at 3:57 am
Hi,
Direct affect ONE
and by trigger n nos of tables
ARUN SAS
April 3, 2009 at 6:12 am
Yep. One at a time.
You can have a procedure that affects lots of tables, but that procedure will have individual statements for each table within it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 5, 2009 at 1:13 am
One INSERT, UPDATE or DELETE statement (assuming no triggers) can INSERT to two tables if an OUTPUT clause into a table variable is used 😀 😀 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2009 at 3:03 pm
While that does allow a single procedure to act on multiple tables, you're still only getting a single table per insert statement. You're just rigging the insert statements to sort of cascade.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 5, 2009 at 3:27 pm
Grant Fritchey (4/5/2009)
While that does allow a single procedure to act on multiple tables, you're still only getting a single table per insert statement. You're just rigging the insert statements to sort of cascade.
😉 Nope - I meant that there is one INSERT / UPDATE/ DELETE on the main table, plus an INSERT to the table variable...see what I did there?! 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 5, 2009 at 11:44 pm
Hi Guys
Thanks for the response. But with the response some other question pitched in for me,
Suppose I write a delete statement and that table has a cascade delete to other table.
So when I delete entry in parent table , child table gets deleted and if the child table is a parent table for other then that also will be deleted.
Assume the casacading effect continues....
How long can this delete happen?
Is there any count constraint for it?
April 6, 2009 at 12:33 am
Hi
You are correct a deletion in parent table may cause deletes in related tables and their relations.
How long this can take? Anytime between 0 (nearly) and forever. E.g. if you have locks. If you have many relations to your parent record a transaction can become as huge that is will never response. There have been several threads about this.
If you want to delete parent records with many child records which takes to long you may try delete bottom up.
Greets
Flo
April 6, 2009 at 1:35 am
metoseeu (4/5/2009)
Assume the casacading effect continues....How long can this delete happen?
Is there any count constraint for it?
Hey there,
I'm going to assume you are asking how many times an action (DELETE or UPDATE) can cascade - that is, ho many levels deep can it go.
I haven't tested this, but I would not be surprised if there were a limit of 32 levels - that is the nesting limit on ordinary triggers.
The current nest level is returned by the @@NESTLEVEL function. It might be difficult to get at that because the internal triggers that implement cascading updates and deletes are not modifiable by mere sysadmins.
It might be possible to check the @@NESTLEVEL on an INSTEAD OF trigger on the child table (or maybe even on an AFTER trigger) though this depends on precisely when user triggers are called in relation to the internal triggers.
I hope that makes sense. I may do some tests on this if I get a moment. If so, I will post back.
I must admit I am intrigued as to why you ask this question. I do hope you are not planning anything hacky! Note that recursive cascades are not allowed, and each table can only be the destination of one cascade (if I recall correctly).
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2009 at 1:49 am
Well I was 100% wrong!
Cascades do not have a limit of 32 levels.
@@NESTLEVEL is not updated.
CREATE DATABASE [BCE86EC3-7D5C-4D73-B5BE-D9295C7C0CB1];
GO
USE [BCE86EC3-7D5C-4D73-B5BE-D9295C7C0CB1];
GO
CREATE TABLE A (A INT PRIMARY KEY);
CREATE TABLE B (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE C (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE D (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE E (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE F (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE G (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE H (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE I (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE J (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE K (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE L (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE M (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE N (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE O (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE P (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE Q (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE R (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE S (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE T (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE U (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE V (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE W (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE X (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE Y (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE Z (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE AA (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE AB (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE AC (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE AD (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE AE (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE AF (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
CREATE TABLE AG (A INT NOT NULL FOREIGN KEY REFERENCES A ON DELETE CASCADE, UNIQUE CLUSTERED (A))
GO
CREATE TRIGGER trg_Test_AD ON AG AFTER DELETE AS BEGIN SELECT @@NESTLEVEL AS NL END
GO
INSERT A (A) VALUES(1)
INSERT B (A) VALUES(1)
INSERT C (A) VALUES(1)
INSERT D (A) VALUES(1)
INSERT E (A) VALUES(1)
INSERT F (A) VALUES(1)
INSERT G (A) VALUES(1)
INSERT H (A) VALUES(1)
INSERT I (A) VALUES(1)
INSERT J (A) VALUES(1)
INSERT K (A) VALUES(1)
INSERT L (A) VALUES(1)
INSERT M (A) VALUES(1)
INSERT N (A) VALUES(1)
INSERT O (A) VALUES(1)
INSERT P (A) VALUES(1)
INSERT Q (A) VALUES(1)
INSERT R (A) VALUES(1)
INSERT S (A) VALUES(1)
INSERT T (A) VALUES(1)
INSERT U (A) VALUES(1)
INSERT V (A) VALUES(1)
INSERT W (A) VALUES(1)
INSERT X (A) VALUES(1)
INSERT Y (A) VALUES(1)
INSERT Z (A) VALUES(1)
INSERT AA (A) VALUES(1)
INSERT AB (A) VALUES(1)
INSERT AC (A) VALUES(1)
INSERT AD (A) VALUES(1)
INSERT AE (A) VALUES(1)
INSERT AF (A) VALUES(1)
INSERT AG (A) VALUES(1)
-- Test the cascade and trigger
DELETE A WHERE A = 1
GO
-- Tidy up
USE master;
DROP DATABASE [BCE86EC3-7D5C-4D73-B5BE-D9295C7C0CB1]
GO
-- Output: NL = 1!
edit: added database create/drop
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2009 at 2:04 am
Hi Paul!
Dang! You've been to fast 🙂
Only for completeness and not completely senseless writing this test...
SET NOCOUNT ON
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = ''
SELECT TOP(100) @sql = @sql +
'CREATE TABLE Nest' + CONVERT(VARCHAR(5), N) + '(Id INT, ParentId INT, PRIMARY KEY CLUSTERED (Id)' +
CASE WHEN N != 1 THEN ', FOREIGN KEY (ParentId) REFERENCES Nest' + CONVERT(VARCHAR(5), N - 1) + ' (Id) ON DELETE CASCADE' ELSE '' END +
')' + CHAR(13) + CHAR(10) +
' INSERT INTO Nest' + CONVERT(VARCHAR(5), N) + ' SELECT 1, 1' + CHAR(13) + CHAR(10)
FROM Tally
ORDER BY N
EXECUTE(@sql)
GO
DELETE FROM Nest1
---- !!!! FOR CLEAN-UP !!!!
--GO
--DECLARE @sql NVARCHAR(MAX)
--SELECT @sql = ''
--SELECT @sql = @sql + 'DROP TABLE Nest' + CONVERT(VARCHAR(5), N) + CHAR(13) + CHAR(10)
-- FROM Tally
-- WHERE N <= 100
-- ORDER BY N DESC
--EXECUTE (@sql)
Edited:
BTW: Same result. It works with 100 nested FKs.
Greets
Flo
April 6, 2009 at 2:14 am
Nice solution flo!
Typical developer - a code-based solution 😛 😀
...while the DB guy hacks away with cut and paste :laugh:
Yours is great 'cos it goes all the way to 100...and beyond.
Executing the final delete in both our scripts takes a while doesn't it? Not sure I'll be cascading that deep any time soon.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2009 at 2:39 am
Paul White (4/6/2009)
Nice solution flo!Typical developer - a code-based solution 😛 😀
...while the DB guy hacks away with cut and paste :laugh:
Developer rules 😎
Usually I would also do the copy-paste, but I intended the 100 and I'm much too lazy for that. :hehe:
Yours is great 'cos it goes all the way to 100...and beyond.
Executing the final delete in both our scripts takes a while doesn't it? Not sure I'll be cascading that deep any time soon.
Took 96ms on my workstation. For 100 nestings I thought it would take longer.
Greets
Flo
April 6, 2009 at 3:58 am
Hi
Thanks a lot Paul White & Mr or Mrs. 500
The information was amazing.
Just for the information of others reading the post
Paul White Query : Single table with many childs .
Mr or Mrs. 500 Query : Each table created is the parent of next created table . ie A parent of B , B parent of C, C parent of D and goes on....
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply