SET STATISTICS IO OFF

-- Example script showing the problems with forward-pointers
-- Author: Christoffer Hedgate (sql@hedgate.net)

-- Run this script in grid mode to make it easier to see the text (in message tab of course)

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'foo') DROP TABLE foo
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'bar') DROP TABLE bar
GO

CREATE TABLE foo (a int NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, b char(1) NOT NULL, c varchar(4000) NULL)
CREATE TABLE bar (a int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, b char(1) NOT NULL, c varchar(4000) NULL)

DECLARE @msgfoo varchar(8000)
DECLARE @msgbar varchar(8000)
SET @msgfoo = ''
SET @msgbar = ''

SET NOCOUNT ON

PRINT ''
PRINT 'First query, no rows added yet'
SELECT @msgfoo = 'Table foo consists of ' + CAST(dpages AS varchar(10)) + ' data pages' FROM sysindexes WHERE id = OBJECT_ID('foo') AND indid = 0
SELECT @msgbar = 'Table bar consists of ' + CAST(dpages AS varchar(10)) + ' data pages' FROM sysindexes WHERE id = OBJECT_ID('bar') AND indid = 1
PRINT @msgfoo
PRINT @msgbar
PRINT 'Both tables are empty, so 0 pages read'
PRINT ''
SET STATISTICS IO ON
SELECT * FROM foo
SELECT * FROM bar
SET STATISTICS IO OFF


-- Insert values into table foo
INSERT INTO foo (b) VALUES ('a')
INSERT INTO foo (b) VALUES ('b')
INSERT INTO foo (b) VALUES ('c')
INSERT INTO foo (b) VALUES ('d')
INSERT INTO foo (b) VALUES ('e')
INSERT INTO foo (b) VALUES ('f')
INSERT INTO foo (b) VALUES ('g')
INSERT INTO foo (b) VALUES ('h')
INSERT INTO foo (b) VALUES ('i')
INSERT INTO foo (b) VALUES ('j')
INSERT INTO foo (b) VALUES ('k')
INSERT INTO foo (b) VALUES ('l')
INSERT INTO foo (b) VALUES ('m')
INSERT INTO foo (b) VALUES ('n')
INSERT INTO foo (b) VALUES ('o')
INSERT INTO foo (b) VALUES ('p')
INSERT INTO foo (b) VALUES ('q')
INSERT INTO foo (b) VALUES ('r')
INSERT INTO foo (b) VALUES ('s')
INSERT INTO foo (b) VALUES ('t')


-- Insert values into table bar
INSERT INTO bar (b) VALUES ('a')
INSERT INTO bar (b) VALUES ('b')
INSERT INTO bar (b) VALUES ('c')
INSERT INTO bar (b) VALUES ('d')
INSERT INTO bar (b) VALUES ('e')
INSERT INTO bar (b) VALUES ('f')
INSERT INTO bar (b) VALUES ('g')
INSERT INTO bar (b) VALUES ('h')
INSERT INTO bar (b) VALUES ('i')
INSERT INTO bar (b) VALUES ('j')
INSERT INTO bar (b) VALUES ('k')
INSERT INTO bar (b) VALUES ('l')
INSERT INTO bar (b) VALUES ('m')
INSERT INTO bar (b) VALUES ('n')
INSERT INTO bar (b) VALUES ('o')
INSERT INTO bar (b) VALUES ('p')
INSERT INTO bar (b) VALUES ('q')
INSERT INTO bar (b) VALUES ('r')
INSERT INTO bar (b) VALUES ('s')
INSERT INTO bar (b) VALUES ('t')


PRINT ''
PRINT '-----------------------------------------------'
PRINT ''
PRINT 'Second query, both tables have had rows added to them'
SELECT @msgfoo = 'Table foo consists of ' + CAST(dpages AS varchar(10)) + ' data pages' FROM sysindexes WHERE id = OBJECT_ID('foo') AND indid = 0
SELECT @msgbar = 'Table bar consists of ' + CAST(dpages AS varchar(10)) + ' data pages' FROM sysindexes WHERE id = OBJECT_ID('bar') AND indid = 1
PRINT @msgfoo
PRINT @msgbar
PRINT 'Query for table bar needs an extra page read for IAM page'
SET STATISTICS IO ON
SELECT * FROM foo
SELECT * FROM bar
SET STATISTICS IO OFF

-- Update c column of both tables
UPDATE foo SET c = REPLICATE(b, 4000)
UPDATE bar SET c = REPLICATE(b, 4000)


PRINT ''
PRINT ''
PRINT '-----------------------------------------------'
PRINT ''
PRINT 'Third query, data in varchar column of both tables has been extended'
SELECT @msgfoo = 'Table foo consists of ' + CAST(dpages AS varchar(10)) + ' data pages' FROM sysindexes WHERE id = OBJECT_ID('foo') AND indid = 0
SELECT @msgbar = 'Table bar consists of ' + CAST(dpages AS varchar(10)) + ' data pages' FROM sysindexes WHERE id = OBJECT_ID('bar') AND indid = 1
PRINT @msgfoo
PRINT @msgbar
PRINT 'Query for table foo needs lots of extra page reads to follow all forward-pointers back and forth from original data page'
SET STATISTICS IO ON
SELECT * FROM foo
SELECT * FROM bar

PRINT 'However, if we force scan of non-clustered index, page reads goes down to 40 (2 per forwarded row, plus 1 for the first row and 1 for IAM page)'
SELECT * FROM foo WITH (INDEX(2))
SET STATISTICS IO OFF