January 29, 2012 at 5:05 pm
I keep reading people saying an update is essentially a delete combined with an insert. How true is this as far as physical I/O? Like I realize if you update a variable length field it may require restructuring the page that the data is located on, but for a table that has fixed length columns with no nulls, it seems inefficient for a row to be physically removed, then another row added, especially if some of the data is the same. Is SQL Server smart enough to know when it doesn't need to restructure physical data?
Also if I update Column A, but there's a foreign key on Column B, will the database engine still do a look up in the referenced table for the value in Column B, even though I did not make any changes to Column B with code?
January 29, 2012 at 10:24 pm
Gabriel P (1/29/2012)
I keep reading people saying an update is essentially a delete combined with an insert. How true is this as far as physical I/O?
As for as physical I/O is concered, it does not matter. Becuase, all insert/delete/update happens in the memory. Not directly to data file.
When CHECKPOINT happens all the dirty pages are written to data file.
Gabriel P (1/29/2012)
Also if I update Column A, but there's a foreign key on Column B, will the database engine still do a look up in the referenced table for the value in Column B, even though I did not make any changes to Column B with code?
No.
I am sure, others will give answer to your second question and better explanation to the above questions.
January 30, 2012 at 12:31 am
Gabriel P (1/29/2012)
I keep reading people saying an update is essentially a delete combined with an insert.
Not always. Please read the below link....
January 30, 2012 at 1:01 am
Gabriel P (1/29/2012)
I keep reading people saying an update is essentially a delete combined with an insert. How true is this as far as physical I/O?
Any update that changes an index key performs an delete followed by an insert in SQL Server 2005 and later.
http://www.sqlskills.com/BLOGS/PAUL/post/Do-changes-to-index-keys-really-do-in-place-updates.aspx
Physically, only the *net* changes to pages are written to disk (by CHECKPOINT, lazy writer, eager writer, or by normal workers calling HelpLazyWriter), so in most cases it won't really matter that the update is performed on a single page as a delete + insert pair.
Also if I update Column A, but there's a foreign key on Column B, will the database engine still do a look up in the referenced table for the value in Column B, even though I did not make any changes to Column B with code?
No:
USE tempdb;
GO
CREATE TABLE dbo.Parent
(
pid integer PRIMARY KEY
);
GO
CREATE TABLE dbo.Child
(
cid integer PRIMARY KEY,
pid integer NULL REFERENCES dbo.Parent,
some_data integer NOT NULL
);
GO
INSERT dbo.Parent
(pid)
VALUES
(1),
(2);
GO
-- Left Semi Join to check FK
INSERT dbo.Child
(cid, pid, some_data)
VALUES (1, 1, 1001);
GO
-- Left Semi Join to check FK
UPDATE dbo.Child
SET pid = 2
WHERE cid = 1;
GO
-- Simple update
UPDATE dbo.Child
SET some_data = 2001
WHERE cid = 1;
GO
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply