GDI Lord (5/7/2009)
Does the choice of recovery model affect transaction handling in the database?
No, other than for bulk operations (detailed below).
Can I still use transactions?
Yes
Does the transaction log still exist?
Yes. And all transactions will be logged. Logging is not optional and there's no way to stop an operation from logging. The only difference is that some operations are fully logged in full recovery and minimally logged in Simple and Bulk Logged recoveries. See Books Online for more details, look up "minimally logged" or "Bulk logging"
Will the transaction log increase in size over time or is it cleared periodically, and if so, when?
Maybe and yes.
In simple recovery, the log is truncated when a checkpoint operation runs (note, truncated, not shrink). When the log is truncated, inactive log records (those relating to transactions whose changes are hardened on disk) are removed from the log, and the space made available for reuse. However a truncation cannot clear log records where there's log entry that pertains to an open transaction, or to an unreplicated transaction transactional replication), so either of those could prevent truncation and result in the log growing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability