----------------------------------------------------------------
--                                                              
--  File:   Create_DB_Audit_Tables.SQL                          
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      
--          Copyright Microsoft, 2008-2015                  
--                                                              
----------------------------------------------------------------
USE tpce
GO

SET NOCOUNT ON
GO

PRINT   ''
PRINT   ''
PRINT   '-------------------------------------'
PRINT   '|  Creating Database Audit Tables   |'
PRINT   '-------------------------------------'
PRINT   ' '
SELECT  CONVERT(CHAR(30), GETDATE(), 21)

IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'TPCE_SCALING_TABLES' )
    DROP TABLE TPCE_SCALING_TABLES
GO

IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'TPCE_GROWING_TABLES' )
    DROP TABLE TPCE_GROWING_TABLES
GO

IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'TPCE_FIXED_TABLES' )
    DROP TABLE TPCE_FIXED_TABLES
GO

IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'TPCE_AUDIT_OUTPUT' )
    DROP TABLE TPCE_AUDIT_OUTPUT
GO

CREATE TABLE TPCE_SCALING_TABLES    (   NUM_CUST_ROWS           bigint,
                                        NUM_CUST_TAX_ROWS       bigint,
                                        CUST_TAX_TARGET         bigint,
                                        NUM_CUST_ACCT_ROWS      bigint,
                                        CUST_ACCT_TARGET        bigint,
                                        NUM_ACCT_PERM_ROWS      bigint,
                                        ACCT_PERM_TARGET        bigint,
                                        NUM_ADDRESS_ROWS        bigint,
                                        ADDRESS_TARGET          bigint,
                                        NUM_BROKER_ROWS         bigint,
                                        BROKER_TARGET           bigint,
                                        NUM_COMPANY_ROWS        bigint,
                                        COMPANY_TARGET          bigint,
                                        NUM_COMPANY_COMP_ROWS   bigint,
                                        COMPANY_COMP_TARGET     bigint,
                                        NUM_DAILY_MARKET_ROWS   bigint,
                                        DAILY_MARKET_TARGET     bigint,
                                        NUM_FINANCIAL_ROWS      bigint,
                                        FINANCIAL_TARGET        bigint,
                                        NUM_LAST_TRADE_ROWS     bigint,
                                        LAST_TRADE_TARGET       bigint,
                                        NUM_NEWS_ITEM_ROWS      bigint,
                                        NEWS_ITEM_TARGET        bigint,
                                        NUM_NEWS_XREF_ROWS      bigint,
                                        NEWS_XREF_TARGET        bigint,
                                        NUM_SECURITY_ROWS       bigint,
                                        SECURITY_TARGET         bigint,
                                        NUM_WATCH_LIST_ROWS     bigint,
                                        WATCH_LIST_TARGET       bigint,
                                        NUM_WATCH_ITEM_ROWS     bigint,
                                        WATCH_ITEM_TARGET       bigint
                                    )

CREATE TABLE  TPCE_GROWING_TABLES   (   NUM_CASH_TRAN_ROWS      bigint,
                                        CASH_TRAN_TARGET        bigint,
                                        NUM_HOLDING_ROWS        bigint,
                                        HOLDING_TARGET          bigint,
                                        NUM_HOLDING_HIST_ROWS   bigint,
                                        HOLDING_HIST_TARGET     bigint,
                                        NUM_HOLDING_SUMM_ROWS   bigint,
                                        HOLDING_SUMM_TARGET     bigint,
                                        NUM_SETTLEMENT_ROWS     bigint,
                                        SETTLEMENT_TARGET       bigint,
                                        NUM_TRADE_ROWS          bigint,
                                        TRADE_TARGET            bigint,
                                        NUM_TRADE_HIST_ROWS     bigint,
                                        TRADE_HIST_TARGET       bigint,
                                        NUM_TRADE_REQUEST_ROWS  bigint,
                                        TRADE_REQUEST_TARGET    bigint
                                    )

CREATE TABLE  TPCE_FIXED_TABLES     (   NUM_CHARGE_ROWS         bigint,
                                        CHARGE_TARGET           bigint,
                                        NUM_COMM_RATE_ROWS      bigint,
                                        COMM_RATE_TARGET        bigint,
                                        NUM_EXCHANGE_ROWS       bigint,
                                        EXCHANGE_TARGET         bigint,
                                        NUM_INDUSTRY_ROWS       bigint,
                                        INDUSTRY_TARGET         bigint,
                                        NUM_SECTOR_ROWS         bigint,
                                        SECTOR_TARGET           bigint,
                                        NUM_STATUS_TYPE_ROWS    bigint,
                                        STATUS_TYPE_TARGET      bigint,
                                        NUM_TAXRATE_ROWS        bigint,
                                        TAXRATE_TARGET          bigint,
                                        NUM_TRADE_TYPE_ROWS     bigint,
                                        TRADE_TYPE_TARGET       bigint,
                                        NUM_ZIP_CODE_ROWS       bigint,
                                        ZIP_CODE_TARGET         bigint
                                   )

CREATE TABLE TPCE_AUDIT_OUTPUT     (    TABLE_NAME              varchar(50),
                                        ROWS_ACTUAL             bigint,
                                        ROWS_TARGET             bigint,
                                        DELTA                   bigint,
                                        PCT_DELTA               numeric(7,4),
                                        TABLE_TYPE              char(1)
                                    )

PRINT   ''
PRINT   '-------------------------------------'
PRINT   '|   Database Audit Tables Created   |'
PRINT   '-------------------------------------'
PRINT   ' '
SELECT  CONVERT(CHAR(30), GETDATE(), 21)
