/***************************************************************************
* QUERY #1
***************************************************************************/

CREATE TABLE dbo.fiveMillionNumbers (
    n INT PRIMARY KEY
)
GO
INSERT INTO dbo.fiveMillionNumbers WITH (TABLOCK) (n)
SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values v1
CROSS JOIN master..spt_values v2
CROSS JOIN master..spt_values v3
GO

-- Begin a transaction so that we can easily measure log usage
BEGIN TRAN
GO

SET STATISTICS TIME ON
GO

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
FROM dbo.fiveMillionNumbers
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

SET STATISTICS TIME OFF
GO

-- Inspect log usage
SELECT tst.session_id, DB_NAME(tdt.database_id) AS db_name,
    tdt.database_transaction_log_bytes_used / (1024.*1024.) AS log_mb_used
FROM sys.dm_tran_database_transactions tdt
JOIN sys.dm_tran_session_transactions tst
    ON tst.transaction_id = tdt.transaction_id
WHERE tst.session_id = @@SPID
GO

COMMIT
GO

-- Cleanup
DROP TABLE dbo.minimalLoggingTest
GO
DROP TABLE dbo.fiveMillionNumbers
GO


/***************************************************************************
* QUERY #2
***************************************************************************/

CREATE TABLE dbo.fiveMillionNumbersBadEstimate (
    n INT PRIMARY KEY
)
GO
INSERT INTO dbo.fiveMillionNumbersBadEstimate WITH (TABLOCK) (n)
SELECT TOP 5000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values v1
CROSS JOIN master..spt_values v2
CROSS JOIN master..spt_values v3
GO
-- Simulate a complex sub-query or view where SQL Server yields a
-- row estimate of just 1000 rows even though 5MM rows are actually present
UPDATE STATISTICS dbo.fiveMillionNumbersBadEstimate
WITH ROWCOUNT = 1000
GO

-- Begin a transaction so that we can easily measure log usage
BEGIN TRAN
GO

SET STATISTICS TIME ON
GO

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
FROM dbo.fiveMillionNumbersBadEstimate
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

SET STATISTICS TIME OFF
GO

-- Inspect log usage
SELECT tst.session_id, DB_NAME(tdt.database_id) AS db_name,
    tdt.database_transaction_log_bytes_used / (1024.*1024.) AS log_mb_used
FROM sys.dm_tran_database_transactions tdt
JOIN sys.dm_tran_session_transactions tst
    ON tst.transaction_id = tdt.transaction_id
WHERE tst.session_id = @@SPID
GO

COMMIT
GO

-- Cleanup
DROP TABLE dbo.minimalLoggingTest
GO
DROP TABLE dbo.fiveMillionNumbersBadEstimate
GO