OK, went back home and tested. Here is the observation.
CREATE DATABASE TEST;
GO
CREATE TABLE TABLE1
(
ID tinyint,
Details varchar(10)
);
GO
INSERT INTO TABLE1
VALUES (1, 'Original');
GO
SELECT
name,
snapshot_isolation_state_desc,
is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'TEST';
GO
First test with both settings confirmed to be OFF.
Query 1
USE TEST;
BEGIN TRAN
UPDATE TABLE1
SET Details = 'Update'
WHERE ID = 1;
--COMMIT;
--ROLLBACK;
GO
Query 2
USE TEST;
SELECT ID, Details
FROM TABLE1
WHERE ID = 1;
GO
In this test, query 2 is waiting for query 1 to commit, dm_tran_locks DMV shows that exclusive lock on TABLE1 incurred by query 1.
USE TEST;
SELECT
DB_NAME(tl.resource_database_id) AS DBName,
resource_type,
OBJECT_NAME(resource_associated_entity_id) AS tbl_name,
request_mode,
request_status,
request_session_id
FROM sys.dm_tran_locks tl
WHERE
resource_database_id = db_id('TEST')
AND resource_type = 'OBJECT'
Second test, rollback previous transaction, set READ_COMMITTED_SNAPSHOT ON but leave ALLOW_SNAPSHOT_ISOLATION OFF.
ALTER DATABASE TEST
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
GO
Run Query 1, and run query 2. DMV shows query 1 incur exclusive lock, but query 2 returns details with 'Original' without query 1 commit the transaction. It appears that READ_COMMITTED row versioning is in place.
Adding SET TRANSACTION ISOLATION LEVEL SNAPSHOT; on query 1 and query 2, and run query 1 or query 2 returns error - Snapshot isolation transaction failed accessing database 'TEST' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.
Third test, rollback previous transaction. Set READ_COMMITTED_SNAPSHOT OFF and ALLOW_SNAPSHOT_ISOLATION ON.
ALTER DATABASE TEST
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE TEST
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
Run query 1, and then query 2. DMV shows exclusive lock incurred by query 1. Query 2 appears to be waiting for query 1 to complete. Turning ALLOW_SNAPSHOT_ISOLATION ON doesn't appear to enable READ COMMITTED row versioning.
Adding SET TRANSACTION ISOLATION LEVEL SNAPSHOT; to both query 1 and query 2. Run query 1 and then query 2. While DMV shows query 1 incur exclusive lock, query 2 return details with 'Original'. Snapshot isolation appears to be in place.
Observation from the test shows that READ_COMMITTED_SNAPSHOT itself enable/disable the READ COMMITTED row versioning regardless of ALLOW_SNAPSHOT_ISOLATION setting, and vice versa.
