I dont want transaction logs to be created for update statement

Up to my knowledge you cannot turn off transaction log for update.

If update query affects different columns of each row, if transaction log is not there it won't rollback, if it couldn't be trusted to complete an atomic fashion or even at all if it were not being logged.

read this

There is no way to turn off logging.

10,000+ rows doesn't sound like much. If performance is not what you want then take a look at the execution plan to see if creating some indexes could improve things.

I think you may have other things to worry about than the transaction log. Large updates (or many small ones) may cause index fragmentation and statistics that are out of date. This will affect performance in the end. You need to check for index fragmentation on regular basis. And you cannot rely on auto update statistics, because auto update will be triggered after 20% of the rows have been updated / inserted (a little simplified). Inccorect statistics may cause an ineffective execution plan.

I know, this was not the answer you were looking for, but you cannot turn of logging on updates. You can avoid logging in BULK INSERTS, but not updates or deletes.

I had a similar problem where I was updating or deleting millions of records. This would cause problems with log files as well as server performance problems when it was running the update/delete statements. As mentione above, updating fewer records is what I would recommend.

I got round the problem by getting the sql code to delete (or update) the records in batches of 5000 records. A loop was used to delete sets of 5000 records until there were no more to delete/update. This made a big difference with the log file and server performace. I used this approach when I was having to delete in excess 60 million records in one delete statement. This was much quicker and there was not a performance issue.

An example of the delete script I used is as follows (this could easily be modified to update as well as delete). So, if I had a millian records to delete, it would still acheive this automatically by deleteing in batches of 5000 records:

USE MyDb

DECLARE @MyCount As bigint

SET @MyCount = 0

WHILE 1 = 1

BEGIN

  DELETE TOP (5000)

  FROM MyTable

  WHERE <My Condition>

  IF @@ROWCOUNT < 5000 BREAK

  IF @MyCount > 250 BREAK

  SET @MYCount = @MyCount + 1

  Print 'Count is: ' + Str(@MyCount)

END

GO

Also notice that the above script has a break out so that if it attempts to delete more that 250 baches of 5000 records it stops.

Question to Stuart Grace. If I use the following below to update a few million over 200 million records I have to worry about batches?

Use [databasex] Declare @Amount Int

Set @Amount = 5000 repeat:

Update top (@Amount) dbo.tablex

set

--- whatever is being updated

If @@rowcount != 0 goto repeat