CREATE DATABASE COMPRESSIONTEST
GO

USE COMPRESSIONTEST
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransactionHistory_Int]') AND type in (N'U'))
	DROP TABLE [dbo].[TransactionHistory_Int]
GO

CREATE TABLE [dbo].[TransactionHistory_int](
	[TransactionID] [int] IDENTITY(100000,1) NOT NULL,
	[ProductID] [int] NOT NULL,
	[ProductName] [nchar] (50) NOT NULL,
	[ProductNumber] [nvarchar](25) NOT NULL,
	[ReferenceOrderID] [int] NOT NULL,
	[ReferenceOrderLineID] [int] NOT NULL,
	[TransactionDate] [datetime] NOT NULL,
	[TransactionType] [nchar](1) NOT NULL,
	[Quantity] [int] NOT NULL,
	[ActualCost] [money] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL
)
GO

DECLARE @i int = 5

WHILE @i > 0
BEGIN
	INSERT INTO [dbo].[TransactionHistory_int](
		[ProductID] ,
		[ProductName] ,
		[ProductNumber] ,
		[ReferenceOrderID] ,
		[ReferenceOrderLineID] ,
		[TransactionDate] ,
		[TransactionType] ,
		[Quantity] ,
		[ActualCost] ,
		[ModifiedDate] )
	SELECT
		T.[ProductID] ,
		P.[Name] ,
		P.[ProductNumber] ,
		T.[ReferenceOrderID] ,
		T.[ReferenceOrderLineID] ,
		T.[TransactionDate] ,
		T.[TransactionType] ,
		T.[Quantity] ,
		T.[ActualCost] ,
		T.[ModifiedDate] 
	FROM [AdventureWorks2008].[Production].[TransactionHistory] T
	JOIN [AdventureWorks2008].[Production].[Product] P ON T.ProductId = P.ProductId ;
	
	SET @i -= 1  ;
END
GO

ALTER TABLE  [dbo].[TransactionHistory_int] ADD CONSTRAINT [PK_TransactionHistory_int_TransactionID] PRIMARY KEY CLUSTERED 
(
	[TransactionID] ASC
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransactionHistory_guid]') AND type in (N'U'))
	DROP TABLE [dbo].[TransactionHistory_guid]
GO

CREATE TABLE [dbo].[TransactionHistory_guid](
	[TransactionID] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[ProductID] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[ProductName] [nchar] (50) NOT NULL,
	[ProductNumber] [nvarchar](25) NOT NULL,
	[ReferenceOrderID] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[ReferenceOrderLineID] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
	[TransactionDate] [datetime] NOT NULL,
	[TransactionType] [nchar](1) NOT NULL,
	[Quantity] [int] NOT NULL,
	[ActualCost] [money] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL
)
GO

DECLARE @i int = 5

WHILE @i > 0
BEGIN
	INSERT INTO [dbo].[TransactionHistory_guid](
		[ProductName] ,
		[ProductNumber] ,
		[TransactionDate] ,
		[TransactionType] ,
		[Quantity] ,
		[ActualCost] ,
		[ModifiedDate] )
	SELECT
		P.[Name] ,
		P.[ProductNumber] ,
		T.[TransactionDate] ,
		T.[TransactionType] ,
		T.[Quantity] ,
		T.[ActualCost] ,
		T.[ModifiedDate] 
	FROM [AdventureWorks2008].[Production].[TransactionHistory] T
	JOIN [AdventureWorks2008].[Production].[Product] P ON T.ProductId = P.ProductId ;
	
	SET @i -= 1  ;
END
GO

ALTER TABLE  [dbo].[TransactionHistory_guid] ADD CONSTRAINT [PK_TransactionHistory_guid_TransactionID] PRIMARY KEY CLUSTERED 
(
	[TransactionID] ASC
)
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TransactionHistory_sequentialguid]') AND type in (N'U'))
	DROP TABLE [dbo].[TransactionHistory_sequentialguid]
GO

CREATE TABLE [dbo].[TransactionHistory_sequentialguid](
	[TransactionID] [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
	[ProductID] [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
	[ProductName] [nchar] (50) NOT NULL,
	[ProductNumber] [nvarchar](25) NOT NULL,
	[ReferenceOrderID] [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
	[ReferenceOrderLineID] [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
	[TransactionDate] [datetime] NOT NULL,
	[TransactionType] [nchar](1) NOT NULL,
	[Quantity] [int] NOT NULL,
	[ActualCost] [money] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL
)
GO

DECLARE @i int = 5

WHILE @i > 0
BEGIN
	INSERT INTO [dbo].[TransactionHistory_sequentialguid](
		[ProductName] ,
		[ProductNumber] ,
		[TransactionDate] ,
		[TransactionType] ,
		[Quantity] ,
		[ActualCost] ,
		[ModifiedDate] )
	SELECT
		P.[Name] ,
		P.[ProductNumber] ,
		T.[TransactionDate] ,
		T.[TransactionType] ,
		T.[Quantity] ,
		T.[ActualCost] ,
		T.[ModifiedDate] 
	FROM [AdventureWorks2008].[Production].[TransactionHistory] T
	JOIN [AdventureWorks2008].[Production].[Product] P ON T.ProductId = P.ProductId ;
	
	SET @i -= 1  ;
END
GO

ALTER TABLE  [dbo].[TransactionHistory_sequentialguid] ADD CONSTRAINT [PK_TransactionHistory_sequentialguid_TransactionID] PRIMARY KEY CLUSTERED 
(
	[TransactionID] ASC
)
GO

CREATE TABLE #Rslt (
[object_name]    sysname,
[schema_name]    sysname,
[index_id]	int,
[partition_number] int,
[compression_type] char(4) NOT NULL,
[Current Compression(KB)] bigint,
[Requested Compression(KB)] bigint,
[Current Sample Size(KB)] bigint,
[Requested Sample Size(KB)] bigint
)
GO
ALTER TABLE #Rslt ADD CONSTRAINT DF_compression_type DEFAULT ('ROW') FOR [compression_type] ;
GO
 
INSERT INTO #Rslt ( [object_name],
					[schema_name],
					[index_id],
					[partition_number],
					[Current Compression(KB)] ,
					[Requested Compression(KB)],
					[Current Sample Size(KB)] ,
					[Requested Sample Size(KB)]
)
EXEC sp_estimate_data_compression_savings 'dbo', 'TransactionHistory_int', NULL, NULL, 'ROW' ;
GO

INSERT INTO #Rslt ( [object_name],
					[schema_name],
					[index_id],
					[partition_number],
					[Current Compression(KB)] ,
					[Requested Compression(KB)],
					[Current Sample Size(KB)] ,
					[Requested Sample Size(KB)]
)
EXEC sp_estimate_data_compression_savings 'dbo', 'TransactionHistory_guid', NULL, NULL, 'ROW' ;
GO

INSERT INTO #Rslt ( [object_name],
					[schema_name],
					[index_id],
					[partition_number],
					[Current Compression(KB)] ,
					[Requested Compression(KB)],
					[Current Sample Size(KB)] ,
					[Requested Sample Size(KB)]
)
EXEC sp_estimate_data_compression_savings 'dbo', 'TransactionHistory_sequentialguid', NULL, NULL, 'ROW' ;
GO

ALTER TABLE #Rslt DROP CONSTRAINT DF_compression_type ;
GO
ALTER TABLE #Rslt ADD CONSTRAINT DF_compression_type DEFAULT ('PAGE') FOR [compression_type] ;
GO

INSERT INTO #Rslt ( [object_name],
					[schema_name],
					[index_id],
					[partition_number],
					[Current Compression(KB)] ,
					[Requested Compression(KB)],
					[Current Sample Size(KB)] ,
					[Requested Sample Size(KB)]
)
EXEC sp_estimate_data_compression_savings 'dbo', 'TransactionHistory_int', NULL, NULL, 'PAGE' ;
GO


INSERT INTO #Rslt ( [object_name],
					[schema_name],
					[index_id],
					[partition_number],
					[Current Compression(KB)] ,
					[Requested Compression(KB)],
					[Current Sample Size(KB)] ,
					[Requested Sample Size(KB)]
)
EXEC sp_estimate_data_compression_savings 'dbo', 'TransactionHistory_guid', NULL, NULL, 'PAGE' ;
GO

INSERT INTO #Rslt ( [object_name],
					[schema_name],
					[index_id],
					[partition_number],
					[Current Compression(KB)] ,
					[Requested Compression(KB)],
					[Current Sample Size(KB)] ,
					[Requested Sample Size(KB)]
)
EXEC sp_estimate_data_compression_savings 'dbo', 'TransactionHistory_sequentialguid', NULL, NULL, 'PAGE' ;
GO

SELECT [object_name],
	[compression_type] AS [Type],
	[Current Compression(KB)] ,
	[Requested Compression(KB)],
	[Current Sample Size(KB)] ,
	[Requested Sample Size(KB)],
	CAST(100.0 *[Requested Compression(KB)]/[Current Compression(KB)] AS DECIMAL(5,2)) AS [% When compressed]
FROM #Rslt
ORDER BY [Requested Compression(KB)] ASC
GO

--DROP TABLE #Rslt

--USE master
--GO

--DROP DATABASE COMPRESSIONTEST
--GO