----------------------------------------------------------------
--                                                              
--  File:   Create_Indexes_Scaling_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

-- *** DAILY_MARKET ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_DAILY_MARKET' )
    CREATE UNIQUE CLUSTERED INDEX PK_DAILY_MARKET ON DAILY_MARKET(DM_S_SYMB, DM_DATE)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB=ON, MAXDOP = 88)
        ON fixed_fg
GO

CHECKPOINT
GO

-- *** WATCH_ITEM ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_WATCH_ITEM' )
    CREATE UNIQUE CLUSTERED INDEX PK_WATCH_ITEM ON WATCH_ITEM(WI_WL_ID, WI_S_SYMB)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON, MAXDOP = 88)
        ON fixed_fg
GO

CHECKPOINT
GO
 
-- *** FINANCIAL ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_FINANCIAL' )
    CREATE UNIQUE CLUSTERED INDEX PK_FINANCIAL ON FINANCIAL(FI_CO_ID, FI_YEAR, FI_QTR)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON, MAXDOP = 88)
        ON fixed_fg
GO

CHECKPOINT
GO

-- *** ACCOUNT_PERMISSION ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_ACCOUNT_PERMISSION' )
    CREATE UNIQUE CLUSTERED INDEX PK_ACCOUNT_PERMISSION ON ACCOUNT_PERMISSION(AP_CA_ID, AP_TAX_ID)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON, MAXDOP = 88)
        ON fixed_fg
GO

CHECKPOINT
GO

-- *** CUSTOMER_ACCOUNT ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_CUSTOMER_ACCOUNT' )
    CREATE UNIQUE CLUSTERED INDEX PK_CUSTOMER_ACCOUNT ON CUSTOMER_ACCOUNT(CA_ID)
        WITH (FILLFACTOR=82, MAXDOP = 88)
        ON fixed_fg
GO

CHECKPOINT
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_CUSTOMER_ACCOUNT_NC1' )
    CREATE UNIQUE INDEX IX_CUSTOMER_ACCOUNT_NC1 ON CUSTOMER_ACCOUNT(CA_C_ID, CA_ID)
        WITH (FILLFACTOR = 100, MAXDOP = 88)
    	ON fixed_fg
GO

CHECKPOINT
GO

-- *** NEWS_ITEM ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_NEWS_ITEM' )
    CREATE UNIQUE CLUSTERED INDEX PK_NEWS_ITEM ON NEWS_ITEM(NI_ID)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB=ON, MAXDOP = 88)
        ON fixed_fg

GO

CHECKPOINT
GO

-- *** CUSTOMER ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_CUSTOMER' )
    CREATE UNIQUE CLUSTERED INDEX PK_CUSTOMER ON CUSTOMER(C_ID)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB=ON, MAXDOP = 88)
        ON fixed_fg
GO

CHECKPOINT
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_CUSTOMER_NC1' )
    CREATE UNIQUE INDEX IX_CUSTOMER_NC1 ON CUSTOMER(C_TAX_ID, C_ID)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB=ON, MAXDOP = 88)
    	ON fixed_fg
GO

CHECKPOINT
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_CUSTOMER_NC2' )
    CREATE UNIQUE INDEX IX_CUSTOMER_NC2 ON CUSTOMER(C_ID, C_TIER)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB=ON, MAXDOP = 88)
    	ON fixed_fg
GO

CHECKPOINT
GO

-- *** COMPANY ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_COMPANY' )
    CREATE UNIQUE CLUSTERED INDEX PK_COMPANY ON COMPANY(CO_ID)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB=ON, MAXDOP = 88)
        ON fixed_fg
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_COMPANY_NC1' )
    CREATE UNIQUE INDEX IX_COMPANY_NC1 ON COMPANY(CO_NAME, CO_ID)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB=ON, MAXDOP = 88)
    	ON fixed_fg
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_COMPANY_NC2' )
    CREATE UNIQUE INDEX IX_COMPANY_NC2 ON COMPANY(CO_IN_ID, CO_ID)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB=ON, MAXDOP = 88)
    	ON fixed_fg
GO

CHECKPOINT
GO

-- *** SECURITY ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_SECURITY' )
    CREATE UNIQUE CLUSTERED INDEX PK_SECURITY ON SECURITY(S_SYMB)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB=ON, MAXDOP = 88)
        ON fixed_fg
GO
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_SECURITY_NC1' )
    CREATE UNIQUE INDEX IX_SECURITY_NC1 ON SECURITY(S_CO_ID, S_ISSUE, S_EX_ID, S_SYMB)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB=ON, MAXDOP = 88)
    	ON fixed_fg
GO

CHECKPOINT
GO

-- *** ADDRESS ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_ADDRESS' )
    CREATE UNIQUE CLUSTERED INDEX PK_ADDRESS ON ADDRESS(AD_ID)
        WITH (FILLFACTOR = 100, SORT_IN_TEMPDB=ON, MAXDOP = 88)
        ON fixed_fg
GO

CHECKPOINT
GO

-- *** CUSTOMER_TAXRATE ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_CUSTOMER_TAXRATE' )
    CREATE UNIQUE CLUSTERED INDEX PK_CUSTOMER_TAXRATE ON CUSTOMER_TAXRATE(CX_C_ID, CX_TX_ID)
        WITH (FILLFACTOR = 100, MAXDOP = 88)
        ON fixed_fg
GO

-- *** COMPANY_COMPETITOR ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_COMPANY_COMPETITOR' )
    CREATE UNIQUE CLUSTERED INDEX PK_COMPANY_COMPETITOR ON COMPANY_COMPETITOR(CP_CO_ID, CP_COMP_CO_ID, CP_IN_ID)
        WITH (FILLFACTOR = 100, MAXDOP = 88)
        ON fixed_fg
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_COMPANY_COMP_NC1' )
    CREATE INDEX IX_COMPANY_COMP_NC1 ON COMPANY_COMPETITOR(CP_CO_ID)
        WITH (FILLFACTOR = 100, MAXDOP = 88)
    	ON fixed_fg
GO

CHECKPOINT
GO

-- *** LAST_TRADE ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_LAST_TRADE' )
    CREATE UNIQUE CLUSTERED INDEX PK_LAST_TRADE ON LAST_TRADE(LT_S_SYMB)
        WITH (FILLFACTOR = 75, MAXDOP = 88)
        ON fixed_fg
GO

-- *** NEWS_XREF ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_NEWS_XREF' )
    CREATE UNIQUE CLUSTERED INDEX PK_NEWS_XREF ON NEWS_XREF(NX_CO_ID, NX_NI_ID)
        WITH (FILLFACTOR = 100, MAXDOP = 88)
        ON fixed_fg
GO

-- *** WATCH_LIST ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_WATCH_LIST' )
    CREATE UNIQUE CLUSTERED INDEX PK_WATCH_LIST ON WATCH_LIST(WL_ID)
        WITH (FILLFACTOR = 100, MAXDOP = 88)
        ON fixed_fg
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_WATCH_LIST_NC1' )
    CREATE UNIQUE INDEX IX_WATCH_LIST_NC1 ON WATCH_LIST(WL_C_ID, WL_ID)
        WITH (FILLFACTOR = 100, MAXDOP = 88)
        ON fixed_fg
GO

CHECKPOINT
GO

-- *** BROKER ***
IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'PK_BROKER' )
    CREATE UNIQUE CLUSTERED INDEX PK_BROKER ON BROKER(B_ID)
        WITH (FILLFACTOR = 75, MAXDOP = 88)
        ON fixed_fg
GO 

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_BROKER_NC1' )
    CREATE UNIQUE INDEX IX_BROKER_NC1 ON BROKER(B_NAME, B_ID)
        WITH (FILLFACTOR = 100, MAXDOP = 88)
        ON fixed_fg
GO

IF NOT EXISTS ( SELECT name FROM sysindexes WHERE name = 'IX_BROKER_NC2' )
    CREATE UNIQUE INDEX IX_BROKER_NC2 ON BROKER(B_ID, B_NAME)
        WITH (FILLFACTOR = 100, MAXDOP = 88)
        ON fixed_fg
GO

CHECKPOINT
GO

