July 7, 2009 at 5:55 pm
Hey all...I'm not even sure if this is something that I need to really know, it's more for my own curiousity than anything. I'd like to know what SQL Server 2000 does when it encounters an INSERT statement versus an UPDATE statement. I've read a couple of threads in other forums but the answers are contradictory (as an example, in one site I saw that an Update query deletes the existing row and then reinserts, that SQL will scan the row and update only the data that's changed, or that it just overwrites the existing row). Needless to say, I'm a little confused.
Appreciate any clarification you can provide...
Thanks,
Chris
July 13, 2009 at 9:07 am
Not that I have tried it but one way to find out is to create a table then create a Trigger on it for Updates.
When the trigger fires save the contents of the deleted, inserted tables to another table to investigate, it may give you an idea of what happens.
Just a thought.
July 13, 2009 at 9:15 am
Hey Russel - Thanks for replying, I thought for sure my post had been lost in the limbo of the interweb 😛
I'll have to try that. I have never used triggers, so this will be learning two things at once - sweet!!
I'll post back here what i find...
Thanks again!
Chris
July 13, 2009 at 9:33 am
Hi,
The following sql demonstrates how to test this.
It would appear that the column is first deleted from then the new value inserted in its place.
This is just something quick to demonstrate and someone else may prove me wrong but without spending alot of time on it looks pretty close.
-- Create tables to test the actions of an update trigger
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'MyData' and xtype = 'U')
DROP TABLE MyData
GO
CREATE TABLE MyData (
ID INT IDENTITY(1,1),
StringA varchar (30),
StringB varchar (30))
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'tbl_Deleted' and xtype = 'U')
DROP TABLE tbl_Deleted
GO
CREATE TABLE tbl_Deleted (
ID INT,
StringA varchar(30),
StringB Varchar(30),
DateTimeMeasure datetime)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'tbl_Inserted' and xtype = 'U')
DROP TABLE tbl_Inserted
GO
CREATE TABLE tbl_Inserted (
ID INT,
StringA varchar(30),
StringB Varchar(30),
DateTimeMeasure datetime)
GO
-- Insert some dummy data into the table
INSERT INTO MyData (stringA, StringB)
SELECT 'String A Test 1', 'String B Test 1'
GO
--CReate a Trigger on the table being updated
CREATE TRIGGER trg_update_mydata
ON MyData
FOR UPDATE
AS
BEGIN
INSERT INTO tbl_deleted
SELECT deleted.id, deleted.StringA, deleted.StringB, getdate()
FROM deleted
INSERT INTO tbl_inserted
SELECT inserted.id, inserted.StringA, inserted.StringB, getdate()
FROM inserted
END
GO
UPDATE MyData
SET StringA = 'String A Test 1 - Updated'
WHERE StringB = 'String B Test 1'
GO
-- Return what data was inserted
SELECT * FROM tbl_inserted
-- Return what data was deleted
SELECT * FROM tbl_deleted
July 13, 2009 at 9:58 am
The Inside SQL Server book is a great reference and it talks about this. There are in place updates, which just change the data in row, and there are updates that require new rows to be written, because things don't fit on the page.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply