In SQL, Is Update Always Faster Than Delete+Insert

UPDATE vs DELETE and INSERT

The approach normally depends on where you have an OLAP (analytical) or OLTP (transactional) application.

In an OLTP application generally multiple records of a table are concurrently altered under multiple transactions and each transaction is small. Here you should update the records by their PK or any other key having an index.

In an OLAP application generally a large part of the table is altered during ELT by one (or sometimes few non-overlapping) transaction(s). Here delete+insert is more efficient. You would typically be altering many column values so in one shot the whole row can be changed. Since large number of rows are altered, a full table scan is preferred over index access.

Does sql update do a delete then insert?

MSDN Update section:

The Database Engine converts a partial update to a full update when the UPDATE statement causes either of these actions:
Changes a key column of the partitioned view or table.
Modifies more than one row and also updates the key of a nonunique clustered index to a nonconstant value.

And partial update

Partial-update transaction directly writes a character string at a user-defined position of a table column without issuing a delete and replace command, as would happen in a full update.
Partial update does not support multibyte character set conversion. Partial update support is restricted to Microsoft SQL Server.

I did some searching and it looks like the answer to your question depends on what you are updateing.

Hope this helps.

Is it faster to UPDATE a row, or to DELETE it and INSERT a new one?

You should not be asking this question. You are asking "Is it better to do it the right way, or the wrong way, in the name of some nebulous idea of 'faster'?"

Do you have an application that is somehow too slow? Do you for some reason think that the problem is because your UPDATEs are taking too long? Have you done any measurement and benchmarking of the performance of your database interactions?

What you are doing is premature optimization of the worst kind, and you are doing your application a disservice by doing so. You are making wild guesses about how to speed up your code, with absolutely nothing to base it on.

Write your code right. Then try to find where you have a performance problem. Do you even HAVE a performance problem, or are you asking this question simply because you think it's something you should be asking about? You shouldn't.

Even if you specifically DID have a problem with your UPDATEs being too slow, we can't answer the question of "Is X faster than Y" because you have not given us nearly enough information, such as:

What database you are using
The table layouts
What indexes are on the database
How you're interfacing with the database

Please, write your code correctly, and then come back with specifics about what is too slow, rather than guessing at micro-optimizations.

Which is faster insert query or update in sql?

I totally agree with Aakash' answer that UPDATE is most probably the faster approach here.

However I would like to clear up a potential misunderstanding (or lack of understanding) on how a relational database works:

If i need the data for id 90000 then will the sql server search from beginning ID(ID=1) for every select statement or it will directly move to ID=90000

Assuming ID is the primary key in your table (or has a unique index defined) then SQL Server will look up the row with ID=90000 directly (actually pretty much every relational database will do it that way).

Finding the row with id=1 takes the same amount of time as finding the row with ID=90000 or ID=90000000

I suggest you take some time to read the SQL Server manual to understand how a relational database works. The following topics might be interesting for you:

http://msdn.microsoft.com/en-us/library/ms190659%28v=sql.100%29.aspx
http://msdn.microsoft.com/en-us/library/ms190623%28v=sql.100%29.aspx
http://msdn.microsoft.com/en-us/library/bb500155%28v=sql.100%29.aspx

Additionally you might want to have a look at "Use The Index Luke": http://use-the-index-luke.com/

There is a lot of very good information on how indexes work and how the database uses them.

UPDATE vs INSERT performance

I am not a database guru but here my two cents:

Personally I don't think you have much to do in this regard, even if INSERT would be faster (all to be proven), can you convert an update in an insert?! Frankly I don't think you can do it all the times.

During an INSERT you don't usually have to use WHERE to identify which row to update but depending on your indices on that table the operation can have some cost.

During an update if you do not change any column included in any indices you could have quick execution, if the where clause is easy and fast enough.

Nothing is written in stones and really I would imagine it depends on whole database setup, indices and so on.

Anyway, found this one as a reference:

Top 84 MySQL Performance Tips