title: Confusion about SQL Server snapshot isolation and how to use it
body: <p>I am newbie to MS Sql Server. Just a few months experience maintaining SQL Server SPs. I am reading up about transaction isolation levels for optimising an SP and am quite confused. Please help me with below questions:</p>
  
  <ol>
  <li><p>If I run DBCC useroptions in ClientDB, the default value for isolation level is 'read committed'. Does this mean the DB is set to isolation level is set to READ_COMMITTED_SNAPSHOT ON ?</p></li>
  <li><p>Is  SET TRANSACTION ISOLATION LEVEL (at transaction level) same as SET READ_COMMITTED_SNAPSHOT ON (at DB level)? By this I mean that IF my DB has SNAPSHOT enabled, then can I set isolation level in my SP and process data accordingly?</p></li>
  <li><p>is ALLOW_SNAPSHOT_ISOLATION similar to above?</p></li>
  <li><p>I have an SP which starts off with a very long running SELECT statement that also dumps it's contents into a temp table. Then uses the temp table to UPDATE/INSERT base table. There are about 8 mil records being selected and dumped into temp table, then a similar number of total rows updated/inserted. The problem we are facing is that this SP takes up too much disk space.
  It is a client DB and we do not have permissions to check disk space/log size etc in the DB. So I do not know if the tempDB/tempDB-log is taking up this disk space or clientDB/clientDB-log is. But the disk space can reduce by as much as 10GB at one go! This causes the transaction log to run out of disk space (as disk is full) and SP errors out.
  If I use SNAPSHOT isolation level, will this disk space get even more affected? as it uses tempDB to versionize the data?</p></li>
  <li><p>What I really want to do is this:
  SET transaction isolation level to SNAPSHOT. Then do the SELECT into Temp table. Then BEGIN TRANSACTION  and update/insert base table for say ... 1 mil records. Do this in a LOOP until all records processed. Then END TRANSACTION. Do you think this is a good idea? Should the initial SELECT be kept out of the TRANSACTION? Will this help in reducing the load on the transaction logs?</p></li>
  </ol>