-- Demo 1: 0 rows due to CREATE TABLE or TRUNCATE
USE master;

IF db_id('TestIAM') IS NOT NULL
    DROP DATABASE TestIAM;
GO

CREATE DATABASE TestIAM;
GO

USE TestIAM;
GO

CREATE TABLE dbo.TestIAM (TestID INT IDENTITY(1,1) NOT NULL PRIMARY KEY);
GO

-- Returns 0 rows
SELECT *
FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 1, NULL, 'detailed');
GO

INSERT INTO dbo.TestIAM DEFAULT VALUES;
GO

-- Returns 2 rows
SELECT *
FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 1, NULL, 'detailed');
GO

TRUNCATE TABLE dbo.TestIAM;
GO

-- Returns 0 rows
SELECT *
FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 1, NULL, 'detailed');
GO

-- Demo 2: 0 rows due to DELETE FROM
USE master;

IF db_id('TestIAM') IS NOT NULL
    DROP DATABASE TestIAM;
GO

CREATE DATABASE TestIAM;
GO

USE TestIAM;
GO

CREATE TABLE dbo.TestIAM (TestID INT IDENTITY(1,1) NOT NULL PRIMARY KEY);
GO

-- Returns 0 rows
SELECT *
FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 1, NULL, 'detailed');
GO

INSERT INTO dbo.TestIAM DEFAULT VALUES;
GO

-- Returns 2 rows
SELECT *
FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 1, NULL, 'detailed');
GO

DELETE FROM dbo.TestIAM;
GO

-- Returns 2 rows
SELECT *
FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 1, NULL, 'detailed');
GO

-- Demo 3: 0 rows due to SELECT INTO WHERE 0 = 1
USE master;

IF db_id('TestIAM') IS NOT NULL
    DROP DATABASE TestIAM;
GO

CREATE DATABASE TestIAM;
GO

USE TestIAM;
GO

SELECT *
INTO dbo.TestIAM
FROM sys.DATABASES
WHERE 0 = 1;
GO

-- Returns lots o' rows
SELECT *
FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 0, NULL, 'detailed');
GO