Why DELETE statement tend to be slow?
Beetween the field GenderID of the table tblPerson and the field ID of the table tblGender we define a referential integrity.
In T-SQL language we can do this by adding a foreign key to the TblPerson:
ALTER TABLE tblPerson ADD FOREIGN KEY (GenderID)
REFERENCES TblGender(ID);
What does this mean?
It means that the value contained within the TblPerson.GenderId field can only be one of the values contained in the TblGender.ID column.
As the only exception you can put the value NULL if the column GenderID is nullable.
The NULL value means that there is still no value in the column.
So, can you put the value 4 in the GenderID field? NO!
After this brief and really informal explanation let's see with the help of an example what SQL Server does with integrity in the case of UPDATE and DELETE statements
Let's create the two tables for the example:
CREATE TABLE TblGender (
ID INT IDENTITY(1,1),
Gender VARCHAR(20),
PRIMARY KEY(ID)
);
CREATE TABLE tblPerson (
ID INT IDENTITY(1,1),
Name VARCHAR(20),
Email VARCHAR(20),
GenderID int NULL
PRIMARY KEY(ID)
FOREIGN KEY (GenderID) REFERENCES TblGender(ID)
);
Let's insert some data:
INSERT INTO TblGender(Gender) VALUES ('Male'),('Female'),('Unknown')
INSERT INTO TblPerson(Name,Email,GenderID)
VALUES ('Jade','J@j.com',2),('Mary','m@m.com',3),('Martin','ma@ma.com',1)
Now perform an UPDATE
"Miss. Mary changes the sex value from unknown to female"
This is done through this update:
UPDATE TblPerson SET GenderID = 2 WHERE ID = 2
What happens?
Looking at the execution plan we can see that SQL Server uses referential integrity.
This is because it has to understand if the value we want to put inside the column respects the referential integrity or not.
Let us therefore remember that when we make an UPDATE, for each field that we want to update that points to another table (foreign key) its referential integrity will be checked.
From a performance point of view, the more fields that point to another table we update the more time our update takes.
Delete
Let's now try what happens with a DELETE statement by entering this command statement
DELETE FROM TblGender WHERE ID = 2
if you run it, the message below will come out
Msg 547, Level 16, State 0, Line 32
The DELETE statement conflicted with the REFERENCE constraint "FK__tblPerson__Gende__5EBF139D". The conflict occurred in database "Tests", table "dbo.tblPerson", column 'GenderID'.
The statement has been terminated.
The referential integrity will protect our data: You can't delete the row of the table TmlGender with ID equal to 2 because there is at least a row in the table TblPerson where IdGender is equal to 2.
You can see this clearly from the execution plan:
SQL Server will scan the entire Tblperson table searching for a row with GenderID = 2.
From this we understand that to avoid scanning the whole table it is always wise to add a non-clustered index on each column with foreign key.
Finally we turn to the main question: why DELETE statement tend to be slow?
Counterparties, customers, suppliers, products, price lists, documents of any kind?
And if there are many fields to check, the update takes time.
Indeed, most of the time it takes is this Verification.
However, I will try to favor announcements dedicated to this sector or to offers.
Help me to share knowledge on my blog 
Next post:
Previous post: Cursors and a trick before Christmas