row width and version store usage.sql

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com
*****************************************************************************/


IF DB_ID('VersionTest') IS NOT NULL
BEGIN
    use master;

    ALTER DATABASE VersionTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE VersionTest;
END

CREATE DATABASE VersionTest
GO

USE VersionTest;
GO



CREATE TABLE dbo.Narrow (
    i INT IDENTITY not null,
    bitsy BIT,
    j VARCHAR(8000) null,
    k VARCHAR(MAX) null,
    CONSTRAINT cx_Narrow PRIMARY KEY CLUSTERED (i)
);
GO

CREATE TABLE dbo.Wide (
    i INT IDENTITY not null,
    bitsy BIT,
    j CHAR(8000) not null,
    k VARCHAR(MAX) null,
    CONSTRAINT cx_Wide PRIMARY KEY CLUSTERED (i)
);
GO

INSERT dbo.Narrow (bitsy, j, k)
VALUES (1, 'foo', 'foo');
GO
INSERT dbo.Wide (bitsy, j, k)
VALUES (1, 'foo', REPLICATE('foo', 8000));
GO


ALTER DATABASE VersionTest SET ALLOW_SNAPSHOT_ISOLATION ON;
GO


--In another session, run this, leave the transaction open
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRAN
    SELECT * FROM dbo.Narrow;


UPDATE dbo.Narrow
    SET bitsy = 0;
GO

UPDATE dbo.Wide
    SET bitsy = 0;
GO


UPDATE dbo.Wide
    SET bitsy = 0;
GO


--UPDATE dbo.Wide
--    SET k = 'foo';
--GO




SELECT 
    OBJECT_NAME(object_id) as TableName,
    alloc_unit_type_desc,
    max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats 
    (DB_ID(), NULL, NULL, NULL , 'DETAILED')
WHERE object_id IN (OBJECT_ID(N'dbo.Narrow'), OBJECT_ID(N'dbo.Wide'))
GO


select  * 
    from sys.dm_tran_version_store;
GO

--Cleanup

IF DB_ID('VersionTest') IS NOT NULL
BEGIN
    use master;

    ALTER DATABASE VersionTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE VersionTest;
END