----------------------------------------------------------------
--                                                              
--  File:   Create_Indexes_Growing_Tables.SQL                   
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      
--          Copyright Microsoft, 2008-2015                  
--                                                              
--  NOTE:   This file is generated by TPCE_SETUP.CMD based on   
--          the number of processors on the system.             
--                                                              
----------------------------------------------------------------
USE tpce
GO
 
---
--- Set connection attributes
---
SET ANSI_NULLS 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

-- *** TRADE *** 
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_TRADE' )
    CREATE UNIQUE CLUSTERED INDEX PK_TRADE ON TRADE(T_ID)
        WITH (FILLFACTOR = 92, SORT_IN_TEMPDB = ON, MAXDOP = 88)
        ON growing_fg
GO

CHECKPOINT
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_TRADE_NC1' )
    CREATE UNIQUE INDEX IX_TRADE_NC1 ON TRADE(T_CA_ID, T_DTS, T_ID)
        WITH (PAD_INDEX = ON, FILLFACTOR = 90, SORT_IN_TEMPDB = ON, MAXDOP = 88)
        ON growing_fg
GO

CHECKPOINT
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_TRADE_NC2' )
    CREATE UNIQUE INDEX IX_TRADE_NC2 ON TRADE(T_S_SYMB, T_DTS, T_ID)
        WITH (PAD_INDEX = ON, FILLFACTOR = 100, SORT_IN_TEMPDB = ON, MAXDOP = 88)
        ON growing_fg
GO

CHECKPOINT
GO

-- *** CASH_TRANSACTION *** 
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_CASH_TRANSACTION' )
    CREATE UNIQUE CLUSTERED INDEX PK_CASH_TRANSACTION ON CASH_TRANSACTION(CT_T_ID)
        WITH (FILLFACTOR = 95, SORT_IN_TEMPDB=ON, MAXDOP = 88)
        ON growing_fg
GO

CHECKPOINT
GO

-- *** TRADE_HISTORY ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_TRADE_HISTORY' )
    CREATE UNIQUE CLUSTERED INDEX PK_TRADE_HISTORY ON TRADE_HISTORY(TH_T_ID, TH_ST_ID)
        WITH (FILLFACTOR = 95, SORT_IN_TEMPDB=ON, MAXDOP = 88)
        ON growing_fg
GO

CHECKPOINT
GO

-- *** SETTLEMENT *** 
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_SETTLEMENT' )
    CREATE UNIQUE CLUSTERED INDEX PK_SETTLEMENT ON SETTLEMENT(SE_T_ID)
        WITH (FILLFACTOR = 92, SORT_IN_TEMPDB=ON, MAXDOP = 88)
        ON growing_fg
GO

CHECKPOINT
GO

-- *** HOLDING_HISTORY *** 
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_HOLDING_HISTORY' )
    CREATE UNIQUE CLUSTERED INDEX PK_HOLDING_HISTORY ON HOLDING_HISTORY(HH_H_T_ID, HH_T_ID)
        WITH (FILLFACTOR = 95, SORT_IN_TEMPDB = ON, MAXDOP = 88)
        ON growing_fg
GO

CHECKPOINT
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_HOLDING_HISTORY_NC1' )
    CREATE UNIQUE INDEX IX_HOLDING_HISTORY_NC1 ON HOLDING_HISTORY(HH_T_ID, HH_H_T_ID)
        WITH (PAD_INDEX = ON, FILLFACTOR = 90, SORT_IN_TEMPDB = ON, MAXDOP = 88)
        ON growing_fg
GO

CHECKPOINT
GO

-- *** HOLDING *** 
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_HOLDING' )
    CREATE UNIQUE CLUSTERED INDEX PK_HOLDING ON HOLDING(H_T_ID)
        WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, MAXDOP = 88)
        ON growing_fg
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_HOLDING_NC1' )
    CREATE UNIQUE INDEX IX_HOLDING_NC1 ON HOLDING(H_CA_ID, H_S_SYMB, H_DTS, H_T_ID)
        WITH (PAD_INDEX = ON, FILLFACTOR = 90, SORT_IN_TEMPDB = ON, MAXDOP = 88)
        ON growing_fg
GO

CHECKPOINT
GO

-- *** HOLDING_SUMMARY *** 
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_HOLDING_SUMMARY' )
    CREATE UNIQUE CLUSTERED INDEX PK_HOLDING_SUMMARY ON HOLDING_SUMMARY(HS_CA_ID, HS_S_SYMB)
        WITH (FILLFACTOR = 75, SORT_IN_TEMPDB = ON, MAXDOP = 88)
        ON growing_fg
GO

CHECKPOINT
GO

-- *** TRADE_REQUEST *** 
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_TRADE_REQUEST' )
    CREATE UNIQUE CLUSTERED INDEX PK_TRADE_REQUEST ON TRADE_REQUEST(TR_T_ID)
        WITH (FILLFACTOR = 100, MAXDOP = 88)
        ON growing_fg
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_TRADE_REQUEST_NC1' )
    CREATE UNIQUE NONCLUSTERED INDEX IX_TRADE_REQUEST_NC1 ON TRADE_REQUEST(TR_B_ID ASC, TR_S_SYMB ASC, TR_T_ID ASC)
        INCLUDE (TR_BID_PRICE, TR_QTY)
        WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, MAXDOP = 88)
        ON growing_fg
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_TRADE_REQUEST_NC2' )
    CREATE UNIQUE INDEX IX_TRADE_REQUEST_NC2 ON TRADE_REQUEST(TR_S_SYMB, TR_T_ID, TR_TT_ID, TR_BID_PRICE, TR_QTY)
        WITH (FILLFACTOR = 100, MAXDOP = 88)
        ON growing_fg
GO

CHECKPOINT
GO

