| Author |
Topic |
|
Nitu
Yak Posting Veteran
81 Posts |
 Posted - 2006-03-30 : 15:04:13
|
| Hi,Which one of the following is better or rather "RECOMENDED"."UPDATE" or "DELETE + INSERT"If i need to update a record, is it better to use Update directly or do a Delete of that record and then Insert a new Record.Anybody, please let me know.Thanks a lot in advance,Nitu. |
|
|
Tahsin
Starting Member
34 Posts |
Posted - 2006-03-30 : 15:07:52
|
| UPDATE is "better" since you don't have to do a 2 step process and therefore prevent making 2 calls to that table. It will also save a little bit of space on your transaction log for the same reasons. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-31 : 00:36:05
|
| If you want to update child tables, you may need to delete and insertMadhivananFailing to plan is Planning to fail |
|
|
|
pootle_flump
1064 Posts |
Posted - 2006-03-31 : 03:59:45
|
| As a point of interest (or perhaps it isn't) SQL server impliments an UPDATE statement by performing a DELETE and INSERT anyway. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-31 : 04:04:57
|
no it doesn't.it did that in 6.5 but from 7.0 not anymore.Go with the flow & have fun! Else fight the flow  Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-31 : 06:20:24
|
no! i refuse!  Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-31 : 06:24:19
|
| ok joking aside... that article is meant only for replication and sending data from publisher to subscriber.Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
pootle_flump
1064 Posts |
Posted - 2006-03-31 : 07:17:46
|
quote:
Originally posted by spirit1
no it doesn't.it did that in 6.5 but from 7.0 not anymore.
I tried to check my source before posting and couldn't find it immediately so didn't bother. Dug it up now and... the critical bit that didn't crystalise was that it referred to the process if there is an INSERT trigger on the table  http://msdn2.microsoft.com/en-us/library/ms191300(SQL.90).aspx quote:
From BOLAn update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.
|
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-31 : 07:49:31
|
| we'll call it even on the explanations then... what do you say? Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
pootle_flump
1064 Posts |
Posted - 2006-03-31 : 07:51:42
|
Lol - sounds good to me  |
|
|
|
Nitu
Yak Posting Veteran
81 Posts |
Posted - 2006-03-31 : 15:31:41
|
| Intersting..............In terms of performance, which takes more of Sql Servers time. (Update or Delete + Insert)Thanks in advance,Nitu |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-31 : 15:55:59
|
| delete + insert of course.it's still 2 operatios vs 1.you also have to take into account index fragmenting on delete/inserts when there's none in updates if you're not updating a non-clustered indexed column.Go with the flow & have fun! Else fight the flow Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
Nitu
Yak Posting Veteran
81 Posts |
Posted - 2006-03-31 : 16:12:42
|
| Thanks a lot for your response. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-04-05 : 03:14:42
|
| If DELETE+INSERT was better than update you would have to ask yourselfa) Why isn't the UPDATE statement implemented in these terms and therefore equivilentb) Why would there be an UPDATE statement in the first place.b) can be answered by the need to adhere to SQL standards.I think this question is not thought through very well....There is no reason why it would be better (point a) and plenty of reasons why it would not be. I can't begin to go into the hundreds of reasons not to do this. Just think about maintaining RI to start things off!). |
|
|
|
|
Which is better..............