set nocount on;
go

use master;
create database Test001;
go

use Test001;

create table dbo.t1 (id int identity, s varchar(2000));
create table dbo.t2 (id int identity, s varchar(2000));
create table dbo.t3 (id int identity, s varchar(2000));

insert into dbo.t1
 (s)
select top (1000000)
 replicate(a.name + b.name, 20)
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

insert into dbo.t2 (s) select s from dbo.t1;
insert into dbo.t3 (s) select s from dbo.t1;

alter table dbo.t1 add primary key clustered (id);
create clustered columnstore index CIXCS_t2 on dbo.t2;
alter table dbo.t3 add primary key clustered (id);
create columnstore index IXCS_t3 on dbo.t3(s);
go

create function dbo.fnTransactionInfo
(
 @Table sysname
)
returns table
as
return (
 select
  ct.transaction_id, dt.database_transaction_log_record_count, dt.database_transaction_log_bytes_used, grc.ghost_record_count
 from
  sys.dm_tran_current_transaction ct join
  sys.dm_tran_database_transactions dt on dt.transaction_id = ct.transaction_id cross apply
  (select sum(ghost_record_count) as ghost_record_count from sys.dm_db_index_physical_stats(db_id(), object_id(@Table), 1, null, 'detailed')) grc
 where
  dt.database_id = db_id()
);
go

print '---------------------------------------';
print 'Clustered PK only';
print '---------------------------------------';
begin tran;
set statistics io, time on;
delete from dbo.t1 where id between 500000 and 700000;
set statistics io, time off;

select * from dbo.fnTransactionInfo('dbo.t1');
commit;

print '---------------------------------------';
print 'Clustered columnstore only';
print '---------------------------------------';
begin tran;
set statistics io, time on;
delete from dbo.t2 where id between 500000 and 700000;
set statistics io, time off;

select * from dbo.fnTransactionInfo('dbo.t2');
commit;

print '---------------------------------------';
print 'Clustered PK + nonclustered columnstore';
print '---------------------------------------';
begin tran;
set statistics io, time on;
delete from dbo.t3 where id between 500000 and 700000;
set statistics io, time off;

select * from dbo.fnTransactionInfo('dbo.t3');
commit;
go

drop index dbo.t3.IXCS_t3;
go

print '---------------------------------------';
print 'No tablock';
print '---------------------------------------';
begin tran;
delete top (100000) from dbo.t1;

select * from dbo.fnTransactionInfo('dbo.t1');
rollback;

print '---------------------------------------';
print 'Tablock';
print '---------------------------------------';
begin tran;
delete top (100000) from dbo.t1 with (tablock);

select * from dbo.fnTransactionInfo('dbo.t1');
rollback;
go

use master;
drop database Test001;
go

---------------------------------------
Clustered PK only
---------------------------------------
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 't1'. Scan count 1, logical reads 6662, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 156 ms,  elapsed time = 297 ms.
transaction_id       database_transaction_log_record_count database_transaction_log_bytes_used ghost_record_count
-------------------- ------------------------------------- ----------------------------------- --------------------
2206579              200715                                22829504                            5929

---------------------------------------
Clustered columnstore only
---------------------------------------
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 't2'. Scan count 2, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 327, lob physical reads 0, lob read-ahead reads 0.
Table 't2'. Segment reads 2, segment skipped 0.

 SQL Server Execution Times:
   CPU time = 704 ms,  elapsed time = 840 ms.
transaction_id       database_transaction_log_record_count database_transaction_log_bytes_used ghost_record_count
-------------------- ------------------------------------- ----------------------------------- --------------------
2207737              600007                                40378820                            0

---------------------------------------
Clustered PK + nonclustered columnstore
---------------------------------------
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 't3'. Scan count 1, logical reads 6313, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 495 ms.
transaction_id       database_transaction_log_record_count database_transaction_log_bytes_used ghost_record_count
-------------------- ------------------------------------- ----------------------------------- --------------------
2208032              400712                                44178872                            6175

---------------------------------------
No tablock
---------------------------------------
transaction_id       database_transaction_log_record_count database_transaction_log_bytes_used ghost_record_count
-------------------- ------------------------------------- ----------------------------------- --------------------
2209399              101570                                23877972                            11737

---------------------------------------
Tablock
---------------------------------------
transaction_id       database_transaction_log_record_count database_transaction_log_bytes_used ghost_record_count
-------------------- ------------------------------------- ----------------------------------- --------------------
2213584              101646                                23901624                            5930