只要在数据库中启用行版本控制（即快照），所有写入都必须进行版本控制。在发生写入的隔离级别无关紧要，因为隔离级别始终影响只读。启用数据库行版本控制后，任何插入/更新/删除都将：

• 增加每行14字节的数据大小


• 可能会在版本存储（tempdb）中更新之前创建数据的图像

• 表有触发器


• 在连接上启用MARS


• 在表上运行在线索引操作

全部这在行版本控制资源使用中进行了说明：

每个数据库行最多可以在行
版本控制信息的行末使用14
字节。行
版本化信息包含提交版本
的
事务的
事务序列号以及版本化行的指针。
在 任何下，这14个字节添加第一个
时间行被修改或当插入
新行时
的以下条件：

• READ_COMMITTED_SNAPSHOT或ALLOW_SNAPSHOT_ISOLATION选项是
  ON。


• 该表格有一个触发器。


• 正在使用多个活动结果集（MARS）。


• 操作目前正在表格上运行。

...

版本必须存储为
long，因为活动事务需要
访问它。 ...如果它满足
的 任何条件，则满足以下条件：

• 它使用基于行版本的隔离。


• 它使用触发器，MARS或在线索引构建操作。


• / li>
  

更新：

 ：setvar dbname testsnapshot 

使用master; 

如果db_id（'$（dbname）'）不为null 
 begin 
 alter database [$（dbname）] set single_user with rollback immediate; 
 drop database [$（dbname）]; 
 end 
 go 

 create database [$（dbname）]; 
 go 

 use [$（dbname）]; 
 go 


  - 启用行版本控制之前创建表
  -  
创建表t1（i int not null）; 
 go 
插入到t1（i）值（1）; 
 go 

  - 此检查将显示记录不包含版本号
  -  
选择avg_record_size_in_bytes 
从sys.dm_db_index_physical_stats（db_id （），object_id（'t1'），NULL，NULL，'DETAILED'）
  - 记录大小：11（缺少版本信息，至少14字节）

 $ b b  - 启用行版本控制并创建相同的表
  -  
 alter database [$（dbname）] set allow_snapshot_isolation on; 
 go 

创建表t2（i int not null）; 
 go 

设置事务隔离级别读提交; 
 go 

插入到t2（i）值（1）; 
 go 

  - 此检查显示t2中的行具有版本号
  -  
选择avg_record_size_in_bytes 
从sys.dm_db_index_physical_stats（db_id（） ，object_id（'t2'），NULL，NULL，'DETAILED'）
  - 记录大小：25（11 + 14）

  - 此更新将显示版本存储记录
  - 即使隔离级别是读提交
  -  
 begin transaction; 
 update t1 
 set i + = 1; 
 select * from sys.dm_tran_version_store; 
 commit; 
 go 

  - 如果我们再次检查t1的行大小，它的行现在有一个版本号
 select avg_record_size_in_bytes 
从sys.dm_db_index_physical_stats（db_id ），object_id（'t1'），NULL，NULL，'Detailed'）
  - 记录大小：25 
 

Should I run

ALTER DATABASE DbName SET ALLOW_SNAPSHOT_ISOLATION OFF

if snapshot transaction (TX) isolation (iso) is not temporarily used?
In other words,

• why should it be enabled, in first place?
• Why isn't it enabled by default?

What is the cost of having it enabled (but temporarily not used) in SQL Server?

---

--Update:
enabling of snapshot TX iso level on database does not change READ COMMITTED tx iso to be default.
You may check it by running:

use someDbName;
--( 1 )
alter database someDbName set allow_snapshot_isolation ON;
dbcc useroptions;

the last row shows that tx iso level of current session is (read committed).

So, enabling snapshot tx iso level without changing to it does not use it, etc In order to use it one should issue

--( 2 )
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Update2:
I repeat the scripts from [1] but with SNAPSHOT enabled (but not switched on) but without enabling READ_COMMITTED_SNAPSHOT

--with enabling allow_snapshot_isolation
alter database snapshottest set allow_snapshot_isolation ON

-- but without enabling read_committed_snapshot
--alter database snapshottest set read_committed_snapshot ON
-- OR with OFF
alter database snapshottest set read_committed_snapshot OFF 
go

There no results/rows from from executing

select * from sys.dm_tran_version_store

after executing INSERT, DELETE or UPDATE

Can you provide me with scripts illustrating that enabled SNAPSHOT tx iso level by ( 1 ) but not switched on by ( 2 ) produces any versions in tempdb and/or increase the size of data with 14 bytes per row?
Really I do not understand what is the point in versioning if it is enabled by ( 1 ) but not used (not set on by ( 2))?

[1]
Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/12/31/managing-tempdb-in-sql-server-tempdb-basics-version-store-simple-example.aspx

As soon as row versioning (aka. snapshot) is enabled in the database all writes have to be versioned. It doesn't matter under what isolation level the write occurred, since isolation levels always affect only reads. As soon the database row versioning is enabled, any insert/update/delete will:

• increase the size of data with 14 bytes per row
• possibly create an image of the data before the update in the version store (tempdb)

Again, it is completely irrelevant what isolation level is used. Note that row versioning occurs also if any of the following is true:

• table has a trigger
• MARS is enabled on the connection
• Online index operation is running on the table

All this is explained in Row Versioning Resource Usage:

Each database row may use up to 14 bytes at the end of the row for row versioning information. The row versioning information contains the transaction sequence number of the transaction that committed the version and the pointer to the versioned row. These 14 bytes are added the first time the row is modified, or when a new row is inserted, under any of these conditions:

• READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION options are ON.
• The table has a trigger.
• Multiple Active Results Sets (MARS) is being used.
• Online index build operations are currently running on the table.

...

Row versions must be stored for as long as an active transaction needs to access it. ... if it meets any of the following conditions:

• It uses row versioning-based isolation.
• It uses triggers, MARS, or online index build operations.
• It generates row versions.

Update

:setvar dbname testsnapshot

use master;

if db_id('$(dbname)') is not null
begin
    alter database [$(dbname)] set single_user with rollback immediate;
    drop database [$(dbname)];
end
go

create database [$(dbname)];
go

use [$(dbname)];
go


-- create a table before row versioning is enabled
--
create table t1 (i int not null);
go
insert into t1(i) values (1);
go

-- this check will show that the records do not contain a version number
--
select avg_record_size_in_bytes 
from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
-- record size: 11 (lacks version info that is at least 14 bytes)


-- enable row versioning and and create an identical table
--
alter database [$(dbname)] set allow_snapshot_isolation on;
go

create table t2 (i int not null);
go

set transaction isolation level read committed;
go

insert into t2(i) values (1);
go

-- This check shows that the rows in t2 have version number
--
select avg_record_size_in_bytes
     from sys.dm_db_index_physical_stats (db_id(), object_id('t2'), NULL, NULL, 'DETAILED')
-- record size: 25 (11+14)

-- this update will show that the version store has records
-- even though the isolation level is read commited
--
begin transaction;
update t1 
    set i += 1; 
select * from sys.dm_tran_version_store;
commit;
go

-- And if we check again the row size of t1, its rows now have a version number
select avg_record_size_in_bytes
from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
-- record size: 25