Question: I'd like to be able to speed up operations as much as possible by turning off transaction logging completely, but there does not seem to be a way to do it. Why does SQL Server insist on logging all operations, and is it likely that there will be an option to disable logging in future?
Answer: This is a question that comes up regularly and you’re correct that there is no way to disable the generation of transaction log records.
The best you can do is to make use of minimal logging for a small number of operations when using the Simple or Bulk_Logged recovery models, where only data file page allocations are logged, but individual record inserts are not logged. Examples of operations where you can make use of this are index builds/rebuilds and bulk loads. This cuts down on the amount of transaction log generated for the operation and helps make the operations faster. You can read all about minimal logging during data loads in the excellent whitepaper Data Loading Performance Guide.
There are a few operations in tempdb that are truly non-logged (e.g. anything to do with the version store), but generally operations in tempdb generate less transaction log than in user databases because operations in tempdb do not need to log the after image of the operation, only the before image. The after image in a transaction log record is only used for replaying transactions during crash recovery, and as tempdb is not crash-recovered, no replay will ever be performed in tempdb.
Apart from those few operations in tempdb that are non-logged, everything else has to be logged to some degree because SQL Server has to be able to roll back an operation in a database if something goes wrong.
Imagine a situation where SQL Server is part way through an update of 1 million records when the power is accidentally cut to the server. When SQL Server restarts, if there were no log records generated for the update, how can you or SQL Server know how far the update operation progressed before the crash? The simple answer is that you can’t. This means the data is in an inconsistent state and is essentially unusable. The generation of log records allows the update to be rolled back and transactional consistency is maintained.
Even in tempdb, logging is required. If something causes SQL Server to want to roll back an operation in tempdb, but there are no log records, then tempdb is transactionally inconsistent – and SQL Server would have to shut down as it would now know which parts of tempdb were in use or not.
It's always easy to come up with examples of how a non-logged paradigm breaks down, and for that reason I don’t believe we’ll see a non-logged option for SQL Server operations in future.
