------------------------------------------------------------------
--                                                              --
--  File:   Alter_Tables_No_UDDT.SQL                            --
--          Microsoft TPC-E Benchmark Kit Ver. 1.12.0-102X      --
--          Copyright Microsoft  2010, 2011                     --
--                                                              --
------------------------------------------------------------------
USE tpce
GO

---
--- Set connection attributes
---
SET ANSI_NULLS ON
SET ANSI_NULL_DFLT_OFF ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE tpce SET RECOVERY BULK_LOGGED
GO

sp_configure 'max degree of parallelism',0
GO

RECONFIGURE WITH OVERRIDE
GO

SELECT  CONVERT(CHAR(30), GETDATE(), 21)
GO

--
--  Alter the TPC-E scaling tables
--
PRINT ' '
PRINT 'ACCOUNT_PERMISSION'
ALTER TABLE tpce..ACCOUNT_PERMISSION ALTER COLUMN AP_CA_ID bigint NOT NULL
GO

PRINT ' '
PRINT 'ADDRESS'
ALTER TABLE tpce..ADDRESS ALTER COLUMN AD_ID bigint NOT NULL
GO

PRINT ' '
PRINT 'BROKER'
ALTER TABLE tpce..BROKER ALTER COLUMN B_ID bigint NOT NULL
GO

ALTER TABLE tpce..BROKER ALTER COLUMN B_COMM_TOTAL numeric(12,2) NOT NULL
GO

PRINT ' '
PRINT 'COMPANY'
ALTER TABLE tpce..COMPANY ALTER COLUMN CO_ID bigint NOT NULL
GO

ALTER TABLE tpce..COMPANY ALTER COLUMN CO_AD_ID bigint NOT NULL
GO

PRINT ' '
PRINT 'COMPANY_COMPETITOR'
ALTER TABLE tpce..COMPANY_COMPETITOR ALTER COLUMN CP_CO_ID bigint NOT NULL
GO

ALTER TABLE tpce..COMPANY_COMPETITOR ALTER COLUMN CP_COMP_CO_ID bigint NOT NULL
GO

PRINT ' '
PRINT 'CUSTOMER'
ALTER TABLE tpce..CUSTOMER ALTER COLUMN C_ID bigint NOT NULL
GO

ALTER TABLE tpce..CUSTOMER ALTER COLUMN C_AD_ID bigint NOT NULL
GO

PRINT ' '
PRINT 'CUSTOMER_ACCOUNT'
ALTER TABLE tpce..CUSTOMER_ACCOUNT ALTER COLUMN CA_ID bigint NOT NULL
GO

ALTER TABLE tpce..CUSTOMER_ACCOUNT ALTER COLUMN CA_B_ID bigint NOT NULL
GO

ALTER TABLE tpce..CUSTOMER_ACCOUNT ALTER COLUMN CA_C_ID bigint NOT NULL
GO

ALTER TABLE tpce..CUSTOMER_ACCOUNT ALTER COLUMN CA_BAL numeric(12,2) NOT NULL
GO

PRINT ' '
PRINT 'CUSTOMER_TAXRATE'
ALTER TABLE tpce..CUSTOMER_TAXRATE ALTER COLUMN CX_C_ID bigint NOT NULL
GO

PRINT ' '
PRINT 'DAILY_MARKET'
ALTER TABLE tpce..DAILY_MARKET ALTER COLUMN DM_CLOSE numeric(8,2) NOT NULL
GO

ALTER TABLE tpce..DAILY_MARKET ALTER COLUMN DM_HIGH numeric(8,2) NOT NULL
GO

ALTER TABLE tpce..DAILY_MARKET ALTER COLUMN DM_LOW numeric(8,2) NOT NULL
GO

ALTER TABLE tpce..DAILY_MARKET ALTER COLUMN DM_VOL numeric(12) NOT NULL
GO

PRINT ' '
PRINT 'FINANCIAL'
ALTER TABLE tpce..FINANCIAL ALTER COLUMN FI_CO_ID bigint NOT NULL
GO

ALTER TABLE tpce..FINANCIAL ALTER COLUMN FI_REVENUE numeric(15,2) NOT NULL
GO

ALTER TABLE tpce..FINANCIAL ALTER COLUMN FI_NET_EARN numeric(15,2) NOT NULL
GO

ALTER TABLE tpce..FINANCIAL ALTER COLUMN FI_BASIC_EPS numeric(10,2) NOT NULL
GO

ALTER TABLE tpce..FINANCIAL ALTER COLUMN FI_DILUT_EPS numeric(10,2) NOT NULL
GO

ALTER TABLE tpce..FINANCIAL ALTER COLUMN FI_MARGIN numeric(10,2) NOT NULL
GO

ALTER TABLE tpce..FINANCIAL ALTER COLUMN FI_INVENTORY numeric(15,2) NOT NULL
GO

ALTER TABLE tpce..FINANCIAL ALTER COLUMN FI_ASSETS numeric(15,2) NOT NULL
GO

ALTER TABLE tpce..FINANCIAL ALTER COLUMN FI_LIABILITY numeric(15,2) NOT NULL
GO

ALTER TABLE tpce..FINANCIAL ALTER COLUMN FI_OUT_BASIC numeric(12) NOT NULL
GO

ALTER TABLE tpce..FINANCIAL ALTER COLUMN FI_OUT_DILUT numeric(12) NOT NULL
GO

PRINT ' '
PRINT 'LAST_TRADE'
ALTER TABLE tpce..LAST_TRADE ALTER COLUMN LT_PRICE numeric(8,2) NOT NULL
GO

ALTER TABLE tpce..LAST_TRADE ALTER COLUMN LT_OPEN_PRICE numeric(8,2) NOT NULL
GO

ALTER TABLE tpce..LAST_TRADE ALTER COLUMN LT_VOL numeric(12) NOT NULL
GO

PRINT ' '
PRINT 'NEWS_ITEM'
ALTER TABLE tpce..NEWS_ITEM ALTER COLUMN NI_ID bigint NOT NULL
GO

ALTER TABLE tpce..NEWS_ITEM ALTER COLUMN NI_ITEM varbinary(max) NOT NULL
GO

PRINT ' '
PRINT 'NEWS_XREF'
ALTER TABLE tpce..NEWS_XREF ALTER COLUMN NX_NI_ID bigint NOT NULL
GO

ALTER TABLE tpce..NEWS_XREF ALTER COLUMN NX_CO_ID bigint NOT NULL
GO

PRINT ' '
PRINT 'SECURITY'
ALTER TABLE tpce..SECURITY ALTER COLUMN S_CO_ID bigint NOT NULL
GO

ALTER TABLE tpce..SECURITY ALTER COLUMN S_NUM_OUT numeric(12) NOT NULL
GO

ALTER TABLE tpce..SECURITY ALTER COLUMN S_PE numeric(10,2) NOT NULL
GO

ALTER TABLE tpce..SECURITY ALTER COLUMN S_52WK_HIGH numeric(8,2) NOT NULL
GO

ALTER TABLE tpce..SECURITY ALTER COLUMN S_52WK_LOW numeric(8,2) NOT NULL
GO

ALTER TABLE tpce..SECURITY ALTER COLUMN S_DIVIDEND numeric(10,2) NOT NULL
GO

PRINT ' '
PRINT 'WATCH_ITEM'
ALTER TABLE tpce..WATCH_ITEM ALTER COLUMN WI_WL_ID bigint NOT NULL
GO

PRINT ' '
PRINT 'WATCH_LIST'
ALTER TABLE tpce..WATCH_LIST ALTER COLUMN WL_ID bigint NOT NULL
GO

ALTER TABLE tpce..WATCH_LIST ALTER COLUMN WL_C_ID bigint NOT NULL
GO

--
--  Alter the TPC-E growing tables
--
PRINT ' '
PRINT 'CASH_TRANSACTION'
ALTER TABLE tpce..CASH_TRANSACTION ALTER COLUMN CT_T_ID bigint NOT NULL
GO

ALTER TABLE tpce..CASH_TRANSACTION ALTER COLUMN CT_AMT numeric(10,2) NOT NULL
GO

PRINT ' '
PRINT 'HOLDING'
ALTER TABLE tpce..HOLDING ALTER COLUMN H_T_ID bigint NOT NULL
GO

ALTER TABLE tpce..HOLDING ALTER COLUMN H_CA_ID bigint NOT NULL
GO

ALTER TABLE tpce..HOLDING ALTER COLUMN H_PRICE numeric(8,2) NOT NULL
GO

ALTER TABLE tpce..HOLDING ALTER COLUMN H_QTY numeric(6) NOT NULL
GO

PRINT ' '
PRINT 'HOLDING_HISTORY'
ALTER TABLE tpce..HOLDING_HISTORY ALTER COLUMN HH_H_T_ID bigint NOT NULL
GO

ALTER TABLE tpce..HOLDING_HISTORY ALTER COLUMN HH_T_ID bigint NOT NULL
GO

ALTER TABLE tpce..HOLDING_HISTORY ALTER COLUMN HH_BEFORE_QTY numeric(6) NOT NULL
GO

ALTER TABLE tpce..HOLDING_HISTORY ALTER COLUMN HH_AFTER_QTY numeric(6) NOT NULL
GO

PRINT ' '
PRINT 'HOLDING_SUMMARY'
ALTER TABLE tpce..HOLDING_SUMMARY ALTER COLUMN HS_CA_ID bigint NOT NULL
GO

ALTER TABLE tpce..HOLDING_SUMMARY ALTER COLUMN HS_QTY numeric(6) NOT NULL
GO

PRINT ' '
PRINT 'SETTLEMENT'
ALTER TABLE tpce..SETTLEMENT ALTER COLUMN SE_T_ID bigint NOT NULL
GO

ALTER TABLE tpce..SETTLEMENT ALTER COLUMN SE_AMT numeric(10,2) NOT NULL
GO

PRINT ' '
PRINT 'TRADE'
ALTER TABLE tpce..TRADE ALTER COLUMN T_ID bigint NOT NULL
GO

ALTER TABLE tpce..TRADE ALTER COLUMN T_IS_CASH tinyint NOT NULL
GO

ALTER TABLE tpce..TRADE ALTER COLUMN T_QTY numeric(6) NOT NULL
GO

ALTER TABLE tpce..TRADE ALTER COLUMN T_BID_PRICE numeric(8,2) NOT NULL
GO

ALTER TABLE tpce..TRADE ALTER COLUMN T_CA_ID bigint NOT NULL
GO

ALTER TABLE tpce..TRADE ALTER COLUMN T_TRADE_PRICE numeric(8,2)
GO

ALTER TABLE tpce..TRADE ALTER COLUMN T_CHRG numeric(10,2) NOT NULL
GO

ALTER TABLE tpce..TRADE ALTER COLUMN T_COMM numeric(10,2) NOT NULL
GO

ALTER TABLE tpce..TRADE ALTER COLUMN T_TAX numeric(10,2) NOT NULL
GO

ALTER TABLE tpce..TRADE ALTER COLUMN T_LIFO tinyint NOT NULL
GO

PRINT ' '
PRINT 'TRADE_HISTORY'
ALTER TABLE tpce..TRADE_HISTORY ALTER COLUMN TH_T_ID bigint NOT NULL
GO

PRINT ' '
PRINT 'TRADE_REQUEST'
ALTER TABLE tpce..TRADE_REQUEST ALTER COLUMN TR_T_ID bigint NOT NULL
GO

ALTER TABLE tpce..TRADE_REQUEST ALTER COLUMN TR_QTY numeric(6) NOT NULL
GO

ALTER TABLE tpce..TRADE_REQUEST ALTER COLUMN TR_BID_PRICE numeric(8,2) NOT NULL
GO

ALTER TABLE tpce..TRADE_REQUEST ALTER COLUMN TR_B_ID bigint NOT NULL
GO

--
--  Alter the fixed TPC-E tables
--
PRINT ' '
PRINT 'CHARGE'
ALTER TABLE tpce..CHARGE ALTER COLUMN CH_CHRG numeric(10,2) NOT NULL
GO

PRINT ' '
PRINT 'COMMISSION_RATE'
ALTER TABLE tpce..COMMISSION_RATE ALTER COLUMN CR_FROM_QTY numeric(6) NOT NULL
GO

ALTER TABLE tpce..COMMISSION_RATE ALTER COLUMN CR_TO_QTY numeric(6) NOT NULL
GO

PRINT ' '
PRINT 'ECHANGE'
ALTER TABLE tpce..EXCHANGE ALTER COLUMN EX_AD_ID bigint NOT NULL
GO

PRINT ' '
PRINT 'TRADE_TYPE'
ALTER TABLE tpce..TRADE_TYPE ALTER COLUMN TT_IS_SELL tinyint NOT NULL
GO

ALTER TABLE tpce..TRADE_TYPE ALTER COLUMN TT_IS_MRKT tinyint NOT NULL
GO

SELECT  CONVERT(CHAR(30), GETDATE(), 21)
GO

ALTER DATABASE tpce SET RECOVERY FULL
GO

sp_configure 'max degree of parallelism',1
GO

RECONFIGURE WITH OVERRIDE
GO
