/*============================================================================
  File:     Viewing Partition Information.sql

  Summary:  Heap structures v. Clustered Structures - how can you determine
			data placement and in_row v. lob_data.

  Date:     June 2005

  SQL Server Version: 9.00.1116.08 (April CTP)
------------------------------------------------------------------------------
  Copyright (C) 2005 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- Use a "TestDB" Database with at Least 3-8 MB Free Space.
USE TestFGDB
go

SET STATISTICS IO OFF
-- Turn Graphical Showplan ON (Ctrl+K)
SET NOCOUNT ON
go
IF OBJECTPROPERTY(object_id('dbo.DemoStructuresHeap'), 'IsUserTable') = 1
	DROP TABLE dbo.DemoStructuresHeap
go
CREATE TABLE dbo.DemoStructuresHeap
(
	col1	int	 		identity(100,10),
	col2	datetime 	CONSTRAINT DemoStructuresHeapCol2Default 
							DEFAULT current_timestamp,
	col3	datetime 	CONSTRAINT DemoStructuresHeapCol3Default 
							DEFAULT getdate(),
	col4	char(30) 	CONSTRAINT DemoStructuresHeapCol4Default 
							DEFAULT suser_name(),
	col5	char(30)  	CONSTRAINT DemoStructuresHeapCol5Default 
							DEFAULT user_name(),
	col6	nchar(100)  	CONSTRAINT DemoStructuresHeapCol6Default 
							DEFAULT N'Long text value of "Now is the time for all good men to come to the aid of their country."',
	col7	nvarchar(2000)  	CONSTRAINT DemoStructuresHeapCol7Default 
							DEFAULT N'narrow col7',
	col8	nvarchar(4000)  	CONSTRAINT DemoStructuresHeapCol8Default 
							DEFAULT N'narrow col8'
)
go
DECLARE @EndTime	datetime
SELECT @EndTime = dateadd(ss, 40, getdate())
WHILE getdate() <= @EndTime
BEGIN
	INSERT dbo.DemoStructuresHeap DEFAULT VALUES
END
go

EXEC sp_spaceused 'dbo.DemoStructuresHeap', true
go
-- Record the RowCount = 79623            
-- Figure out the number of pages by dividing "data" by 8
-- SELECT 27696/8 = 3463 pages

SET STATISTICS IO ON
go

SELECT count(*) FROM dbo.DemoStructuresHeap
go

-- Create a new table that will be clustered...
SELECT * 
INTO DemoStructuresCLInd
FROM DemoStructuresHeap
go

ALTER TABLE DemoStructuresCLInd
ADD	CONSTRAINT DemoStructuresCLIndPK
		PRIMARY KEY CLUSTERED (Col1)
go

sp_spaceused DemoStructuresHeap, true
exec sp_spaceused DemoStructuresCLInd, true
go

SELECT p.* 
FROM sys.partitions AS p
WHERE p.[object_id] = object_id('DemoStructuresHeap')
go

SELECT p.* 
FROM sys.partitions AS p
WHERE p.[object_id] = object_id('DemoStructuresCLInd')
go

SELECT * 
FROM sys.allocation_units AS au
	JOIN sys.partitions AS p
		ON au.container_id = p.partition_id
WHERE p.[object_id] = object_id('DemoStructuresHeap')
go

SELECT * 
FROM sys.allocation_units AS au
	JOIN sys.partitions AS p
		ON au.container_id = p.partition_id
WHERE p.[object_id] = object_id('DemoStructuresCLInd')
go

SELECT * FROM sys.dm_db_partition_stats 
WHERE [object_id] = object_id('DemoStructuresCLInd')
go

SELECT * 
FROM sys.dm_db_index_physical_stats
	(db_id('TestFGDB')
	, object_id('TestFGDB.dbo.DemoStructuresHeap')
	, NULL
	, NULL
	, 'DETAILED')
go

SELECT * 
FROM sys.dm_db_index_physical_stats
	(db_id('TestFGDB')
	, object_id('TestFGDB.dbo.DemoStructuresCLInd')
	, NULL
	, NULL
	, 'DETAILED')
go

UPDATE dbo.DemoStructuresHeap
	SET Col7 = 'This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :). This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :). This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :).This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :). This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :). This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :). This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :).This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :).'
WHERE Col1 % 37 = 0
go

UPDATE DemoStructuresCLInd
	SET Col7 = 'This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :). This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :). This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :).This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :). This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :). This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :). This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :).This is a test to see what happens when a row modification MIGHT cause a split OR might cause overflow and well the idea is that this will allow a row to still fit on a page but we''re not really sure of the exact result here :).'
WHERE Col1 % 37 = 0
go

sp_spaceused DemoStructuresHeap, true
exec sp_spaceused DemoStructuresCLInd, true
go
 
SELECT * 
FROM sys.dm_db_index_physical_stats
	(db_id('TestFGDB')
	, object_id('TestFGDB.dbo.DemoStructuresHeap')
	, NULL
	, NULL
	, 'DETAILED')
go

SELECT * 
FROM sys.dm_db_index_physical_stats
	(db_id('TestFGDB')
	, object_id('TestFGDB.dbo.DemoStructuresCLInd')
	, NULL
	, NULL
	, 'DETAILED')
go

-- Create row_overflow (only occurs for rows over 8060 bytes)
UPDATE dbo.DemoStructuresHeap
	SET Col8 = replicate ('*', 3000) 
WHERE Col1 % 63 = 0
go

UPDATE DemoStructuresCLInd
	SET Col8 = replicate ('*', 3000) 
WHERE Col1 % 63 = 0
go

SELECT * 
FROM sys.allocation_units AS au
	JOIN sys.partitions AS p
		ON au.container_id = p.partition_id
WHERE p.[object_id] = object_id('DemoStructuresHeap')
go

SELECT * 
FROM sys.allocation_units AS au
	JOIN sys.partitions AS p
		ON au.container_id = p.partition_id
WHERE p.[object_id] = object_id('DemoStructuresCLInd')
go

UPDATE dbo.DemoStructuresHeap
	SET Col8 = replicate ('*', 4000) 
WHERE Col1 = 100
go

SELECT * 
FROM sys.allocation_units AS au
	JOIN sys.partitions AS p
		ON au.container_id = p.partition_id
WHERE p.[object_id] = object_id('DemoStructuresHeap')
go

SET STATISTICS IO OFF
go

sp_spaceused DemoStructuresHeap, true
exec sp_spaceused DemoStructuresCLInd, true
go

--SELECT 76752/8 = 9594
--SELECT 114992/8 = 14374

SET STATISTICS IO ON
go

SET STATISTICS TIME ON
go
SELECT count(*) FROM dbo.DemoStructuresHeap
SELECT count(*) FROM dbo.DemoStructuresCLInd
go
