USE JunkDB
go

CREATE TABLE [testfixedwidth]
(
	col1	int				identity,
	col2	char(8000)		default 'slob',
	col3	char(8000)		default 'slob',
	col4	char(8000)		default 'slob'
) -- fails
go

CREATE TABLE [testfixedwidth]
(
	col1	int				identity,
	col2	varchar(8000)	default 'slob',
	col3	varchar(8000)	default 'slob',
	col4	varchar(8000)	default 'slob'
)
go

INSERT testfixedwidth VALUES (REPLICATE('x', 8000), REPLICATE('x', 8000), REPLICATE('x', 8000))
go 10

DBCC IND ('JunkDB', 'testfixedwidth', 1);
GO

DBCC TRACEON (3604);
GO

DBCC PAGE (Junkdb, 4, 9, 3); 
GO

set statistics io on
select * from testfixedwidth
select * from sys.dm_db_index_physical_stats(db_id(), object_id('testfixedwidth'), null, null, null)
select * from sys.dm_db_index_physical_stats(db_id(), object_id('testfixedwidth'), null, null, 'sampled')
select * from sys.dm_db_index_physical_stats(db_id(), object_id('testfixedwidth'), null, null, 'detailed')

DROP TABLE [testoverflow&lob]
go

CREATE TABLE [testoverflow&lob]
(
	col1	int				identity,
	col2	varchar(8000)	default 'slob',
	col3	varchar(8000)	default 'slob',
	col4	varchar(8000)	default 'slob',
	col5	varchar(8000)	default 'slob',
	col6	varchar(max)	default 'lob',
	col7	varchar(max)	default 'lob',
	col8	varchar(max)	default 'lob'
)
go

INSERT [testoverflow&lob] DEFAULT VALUES -- all data can fit in_row
go

SELECT * FROM sys.dm_db_index_physical_stats 
(db_id(), object_id('testoverflow&lob'), null, null, 'detailed')
go

DBCC IND ('JunkDB', 'testoverflow&lob', 1);
GO

DBCC TRACEON (3604);
GO

DBCC PAGE (Junkdb, 1, 188, 3); -- IAM page
GO

DBCC PAGE (Junkdb, 1, 187, 3); -- Data page
go

INSERT [testoverflow&lob] (col2)	-- still not big enough to overflow
VALUES (REPLICATE('a', 7900))
go

DBCC IND ('JunkDB', 'testoverflow&lob', 1);
GO

DBCC PAGE (Junkdb, 1, 189, 3); -- Data page
go

SELECT * FROM sys.dm_db_index_physical_stats (db_id(), object_id('testoverflow&lob'), null, null, 'detailed')
go

INSERT [testoverflow&lob] (col2, col3)	-- this will cause overflow
VALUES (REPLICATE('x', 8000), REPLICATE('x', 2000))
go

SELECT * FROM sys.dm_db_index_physical_stats (db_id(), object_id('testoverflow&lob'), null, null, 'detailed')
go

DBCC IND ('JunkDB', 'testoverflow&lob', 1);
GO

DBCC PAGE (Junkdb, 1, 187, 3); 
go

DBCC PAGE (Junkdb, 1, 189, 3); 
go

set statistics io on
go
select * from [testoverflow&lob]

INSERT [testoverflow&lob] (col6, col7)	-- lobs are in_row IF they fit...
VALUES (REPLICATE('a', 3000), REPLICATE('a', 3000))
go

SELECT * FROM sys.dm_db_index_physical_stats (db_id(), object_id('testoverflow&lob'), null, null, 'detailed')
go

INSERT [testoverflow&lob] (col6, col7)	-- lobs are in_row IF they fit...
VALUES (REPLICATE('a', 5000), REPLICATE('a', 5000))
go

SELECT * FROM sys.dm_db_index_physical_stats (db_id(), object_id('testoverflow&lob'), null, null, 'detailed')
go

DBCC IND ('JunkDB', 'testoverflow&lob', 1);
GO

DBCC PAGE (Junkdb, 1, 201, 3); -- Lob page... but, from which row? Noe, no back pointer.
go

DBCC PAGE (Junkdb, 1, 203, 3); -- What about the data page...