Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*****************************************************************************
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment