:CONNECT RoadRunner64
--DROP DATABASE [TestRowVersion]

:ON ERROR EXIT

CREATE DATABASE [TestRowVersion]
ON  PRIMARY 
( NAME = N'TestRowVersionData'
	, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\TestRowVersionData.mdf'
	, SIZE = 40MB
	, FILEGROWTH = 10MB)
 LOG ON 
( NAME = N'TestRowVersionLog'
	, FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\TestRowVersionLog.ldf'
	, SIZE = 10MB
	, FILEGROWTH = 10%)
GO

USE TestRowVersion
go

CREATE TABLE LotsOfSmallRows
(
	Col1	int	identity,
	Col2	datetime	default	getdate()
)
go
SET NOCOUNT ON
go

DECLARE @StopTime	datetime
SET @StopTime = dateadd(s, 30, getdate())
WHILE getdate() < @StopTime
INSERT LotsOfSmallRows DEFAULT VALUES
go

SELECT count(*) FROM LotsOfSmallRows
go
DBCC SHOWCONTIG (LotsOfSmallRows)
go
DBCC SHOWCONTIG (LotsOfSmallRows) WITH TABLERESULTS
go
:CONNECT RoadRunner64
USE TestRowversion
CREATE UNIQUE CLUSTERED INDEX LotsOfSmallRowsCLInd ON LotsOfSmallRows (Col1)
go
DBCC SHOWCONTIG (LotsOfSmallRows) WITH TABLERESULTS
go

-- ObjectName                                                                                                                        ObjectId    IndexName                                                                                                                         IndexId     Level       Pages       Rows                   MinimumRecordSize MaximumRecordSize AverageRecordSize                                     ForwardedRecords       Extents     ExtentSwitches AverageFreeBytes                                      AveragePageDensity                                    ScanDensity                                           BestCount   ActualCount LogicalFragmentation                                  ExtentFragmentation                                   
-- --------------------------------------------------------------------------------------------------------------------------------- ----------- --------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ---------------------- ----------------- ----------------- ----------------------------------------------------- ---------------------- ----------- -------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------- ----------- ----------------------------------------------------- ----------------------------------------------------- 
-- LotsOfSmallRows                                                                                                                   1977058079  LotsOfSmallRowsCLInd                                                                                                              1           0           249         95865                  19                19                19.0                                                  0                      33          33             11.0                                                  99.864097595214844                                    94.117647058823522                                    32          34          0.40160644054412842                                   6.0606060028076172

DBCC DBREINDEX (LotsOfSmallRows)
go

-- ObjectName                                                                                                                        ObjectId    IndexName                                                                                                                         IndexId     Level       Pages       Rows                   MinimumRecordSize MaximumRecordSize AverageRecordSize                                     ForwardedRecords       Extents     ExtentSwitches AverageFreeBytes                                      AveragePageDensity                                    ScanDensity                                           BestCount   ActualCount LogicalFragmentation                                  ExtentFragmentation                                   
-- --------------------------------------------------------------------------------------------------------------------------------- ----------- --------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ---------------------- ----------------- ----------------- ----------------------------------------------------- ---------------------- ----------- -------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------- ----------- ----------------------------------------------------- ----------------------------------------------------- 
-- LotsOfSmallRows                                                                                                                   1977058079  LotsOfSmallRowsCLInd                                                                                                              1           0           249         95865                  19                19                19.0                                                  0                      32          31             11.0                                                  99.864097595214844                                    100.0                                                 32          32          0.0                                                   0.0

exec sp_spaceused NULL, 'TRUE'
go

--  
-- database_name                                                                                                                    database_size      unallocated space  
-- -------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------ 
-- TestRowVersionUpdate                                                                                                             120.00 MB          97.47 MB
-- 
--  
-- reserved           data               index_size         unused             
-- ------------------ ------------------ ------------------ ------------------ 
-- 2592 KB            2144 KB            296 KB             152 KB
-- 
:CONNECT RoadRunner64

BACKUP DATABASE TestRowVersion TO DISK = N'C:\SQLTemp\TestRowVersion.bak' WITH INIT
go

-- Processed 352 pages for database 'TestRowVersionUpdate', file 'TestRowVersionUpdate_Data' on file 1.
-- Processed 1 pages for database 'TestRowVersionUpdate', file 'TestRowVersionUpdate_Log' on file 1.
-- BACKUP DATABASE successfully processed 353 pages in 0.720 seconds (4.007 MB/sec).

-- ON YUKON... See Script UpgradeTestRowVersion.sql

-- (1) Restore Database on Yukon (check sp_dbcmptlevel)
-- (2) Check sizes - no disk structures have changed. They are using more of the header than they used to but no data row changes have been made.
-- (3) Enable Row Versioning in the database with *just* statement-level read consistency
-- (4) Update a row and HOLD the transaction
-- (5) Check the size of the table... if every row were modified then the table would be a mess...  so, let's do that!
-- (6) Update the entire table
-- (7) Check fragmentation and table size.