------------------------------------------------------------------
--                                                              --
--  File:   Create_TPCE_TypesSQL                                --
--          Microsoft TPC-E Benchmark Kit Ver. 1.12.0-1022      --
--          Copyright Microsoft  2008, 2009, 2010               --
--                                                              --
------------------------------------------------------------------
USE tpce
GO

---
--- Set connection attributes
---
SET ANSI_NULLS ON
SET ANSI_NULL_DFLT_OFF OFF
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

---
--- if the TPC-E data types do not exist, create them
---
IF NOT EXISTS ( SELECT name FROM sys.types WHERE name = 'TPCE_BOOLEAN' )
    CREATE TYPE TPCE_BOOLEAN FROM tinyint NOT NULL
GO
IF NOT EXISTS ( SELECT name FROM sys.types WHERE name = 'IDENT_T' )
    CREATE TYPE IDENT_T FROM bigint NOT NULL
GO
IF NOT EXISTS ( SELECT name FROM sys.types WHERE name = 'BLOB' )
    CREATE TYPE BLOB FROM varbinary(max) NOT NULL
GO
IF NOT EXISTS ( SELECT name FROM sys.types WHERE name = 'TRADE_T' )
    CREATE TYPE TRADE_T FROM bigint NOT NULL
GO
IF NOT EXISTS ( SELECT name FROM sys.types WHERE name = 'FIN_AGG_T' )
    CREATE TYPE FIN_AGG_T FROM numeric(15,2) NOT NULL
GO
IF NOT EXISTS ( SELECT name FROM sys.types WHERE name = 'S_PRICE_T' )
    CREATE TYPE S_PRICE_T FROM numeric(8,2) 
GO
IF NOT EXISTS ( SELECT name FROM sys.types WHERE name = 'S_COUNT_T' )
    CREATE TYPE S_COUNT_T FROM numeric(12) NOT NULL
GO
IF NOT EXISTS ( SELECT name FROM sys.types WHERE name = 'S_QTY_T' )
    CREATE TYPE S_QTY_T FROM numeric(6) NOT NULL
GO
IF NOT EXISTS ( SELECT name FROM sys.types WHERE name = 'BALANCE_T' )
    CREATE TYPE BALANCE_T FROM numeric(12,2) NOT NULL
GO
IF NOT EXISTS ( SELECT name FROM sys.types WHERE name = 'VALUE_T' )
    CREATE TYPE VALUE_T FROM numeric(10,2) NOT NULL
GO
