--
--  File:     DB_POPULATION.SQL
--

USE tpch3000gcci
GO

SET NOCOUNT ON
GO

PRINT   ''
PRINT   ''
PRINT   '-----------------------------------'
PRINT   '|  DB Primary Key Check - Begin   |'
PRINT   '-----------------------------------'
PRINT   ' '
SELECT  CONVERT(CHAR(30), GETDATE(), 21)
GO
                                    
DECLARE 
        --- Fixed Tables Local Variables ---
        @VAL            bigint,
        @N_NATIONKEY    bigint,
        @R_REGIONKEY    bigint,
        @P_PARTKEY      bigint,
        @S_SUPPKEY      bigint,
        @PS_PARTKEY     bigint,
        @PS_SUPPKEY     bigint,
        @C_CUSTKEY      bigint,
        @O_ORDERKEY     bigint

PRINT   'TPC-H Tables Start'
SELECT  CONVERT(CHAR(30), GETDATE(), 21)
PRINT   ' '

--------------
--- NATION ---
--------------
PRINT   ' '
PRINT   '**************'
PRINT   '*** NATION ***'
PRINT   '**************'
PRINT   ' '
SELECT  COUNT_BIG(*) AS 'Number of NATION Rows'
FROM    NATION

PRINT   ' '
PRINT   'Minimum Primary Key ID From NATION'
SELECT  TOP (1) N_NATIONKEY,
        N_NAME,
        N_REGIONKEY,
        N_COMMENT
FROM    NATION
ORDER   BY N_NATIONKEY     ASC

PRINT   ' '
PRINT   'Maximum Primary Key ID From NATION'
SELECT  TOP (1) N_NATIONKEY,
        N_NAME,
        N_REGIONKEY,
        N_COMMENT
FROM    NATION
ORDER   BY N_NATIONKEY     DESC

-----------------------
--- REGION ---
-----------------------
PRINT   ' '
PRINT   '***********************'
PRINT   '***     REGION      ***'
PRINT   '***********************'
PRINT   ' '
SELECT  COUNT_BIG(*) AS 'Number of REGION Rows'
FROM    REGION

PRINT   ' '
PRINT   'Minimum Primary Key ID From REGION'
SELECT  TOP (1) R_REGIONKEY,
        R_NAME,
        R_COMMENT
FROM    REGION
ORDER   BY R_REGIONKEY     ASC

PRINT   ' '
PRINT   'Maximum Primary Key ID From REGION'
SELECT  TOP (1) R_REGIONKEY,
        R_NAME,
        R_COMMENT
FROM    REGION
ORDER   BY R_REGIONKEY     DESC

-----------------------
---      PART       ---
-----------------------
PRINT   ' '
PRINT   '***********************'
PRINT   '***      PART       ***'
PRINT   '***********************'
PRINT   ' '
SELECT  COUNT_BIG(*) AS 'Number of PART Rows'
FROM    PART

PRINT   ' '
PRINT   'Minimum Primary Key ID From PART'
SELECT  TOP (1) P_PARTKEY,
	    P_NAME,
	    P_MFGR,
	    P_BRAND,
	    P_TYPE,
	    P_SIZE,
	    P_CONTAINER,
	    P_RETAILPRICE,
	    P_COMMENT
FROM    PART
ORDER   BY P_PARTKEY     ASC

PRINT   ' '
PRINT   'Maximum Primary Key ID From PART'
SELECT  TOP (1) P_PARTKEY,
	    P_NAME,
	    P_MFGR,
	    P_BRAND,
	    P_TYPE,
	    P_SIZE,
	    P_CONTAINER,
	    P_RETAILPRICE,
	    P_COMMENT
FROM    PART
ORDER   BY P_PARTKEY     DESC

-----------------------
---    SUPPLIER     ---
-----------------------
PRINT   ' '
PRINT   '***********************'
PRINT   '***    SUPPLIER     ***'
PRINT   '***********************'
PRINT   ' '
SELECT  COUNT_BIG(*) AS 'Number of SUPPLIER Rows'
FROM    SUPPLIER

PRINT   ' '
PRINT   'Minimum Primary Key ID From SUPPLIER'
SELECT  TOP (1) S_SUPPKEY,
	    S_NAME,
	    S_ADDRESS,
	    S_NATIONKEY,
	    S_PHONE,
	    S_ACCTBAL,
	    S_COMMENT
FROM    SUPPLIER
ORDER   BY S_SUPPKEY     ASC

PRINT   ' '
PRINT   'Maximum Primary Key ID From SUPPLIER'
SELECT  TOP (1) S_SUPPKEY,
	    S_NAME,
	    S_ADDRESS,
	    S_NATIONKEY,
	    S_PHONE,
	    S_ACCTBAL,
	    S_COMMENT
FROM    SUPPLIER
ORDER   BY S_SUPPKEY     DESC

-----------------------
---    PARTSUPP     ---
-----------------------
PRINT   ' '
PRINT   '***********************'
PRINT   '***    PARTSUPP     ***'
PRINT   '***********************'
PRINT   ' '
SELECT  COUNT_BIG(*) AS 'Number of PARTSUPP Rows'
FROM    PARTSUPP

PRINT   ' '
PRINT   'Minimum Primary Key ID From PARTSUPP'
SELECT  TOP (1) PS_PARTKEY,
	    PS_SUPPKEY,
	    PS_AVAILQTY,
	    PS_SUPPLYCOST,
	    PS_COMMENT
FROM    PARTSUPP
ORDER   BY PS_PARTKEY   ASC,
        PS_SUPPKEY      ASC    

PRINT   ' '
PRINT   'Maximum Primary Key ID From PARTSUPP'
SELECT  TOP (1) PS_PARTKEY,
	    PS_SUPPKEY,
	    PS_AVAILQTY,
	    PS_SUPPLYCOST,
	    PS_COMMENT
FROM    PARTSUPP
ORDER   BY PS_PARTKEY   DESC, 
        PS_SUPPKEY      DESC

-----------------------
---    CUSTOMER     ---
-----------------------
PRINT   ' '
PRINT   '***********************'
PRINT   '***    CUSTOMER     ***'
PRINT   '***********************'
PRINT   ' '
SELECT  COUNT_BIG(*) AS 'Number of CUSTOMER Rows'
FROM    CUSTOMER

PRINT   ' '
PRINT   'Minimum Primary Key ID From CUSTOMER'
SELECT  TOP (1) C_CUSTKEY,
	    C_NAME,
	    C_ADDRESS,
	    C_NATIONKEY,
	    C_PHONE,
	    C_ACCTBAL,
	    C_MKTSEGMENT,
	    C_COMMENT 
FROM    CUSTOMER
ORDER   BY C_CUSTKEY     ASC

PRINT   ' '
PRINT   'Maximum Primary Key ID From CUSTOMER'
SELECT  TOP (1) C_CUSTKEY,
	    C_NAME,
	    C_ADDRESS,
	    C_NATIONKEY,
	    C_PHONE,
	    C_ACCTBAL,
	    C_MKTSEGMENT,
	    C_COMMENT 
FROM    CUSTOMER
ORDER   BY C_CUSTKEY     DESC

-----------------------
---     ORDERS      ---
-----------------------
PRINT   ' '
PRINT   '***********************'
PRINT   '***     ORDERS      ***'
PRINT   '***********************'
PRINT   ' '
SELECT  COUNT_BIG(*) AS 'Number of ORDERS Rows'
FROM    ORDERS

PRINT   ' '
PRINT   'Minimum Primary Key ID From ORDERS'
SELECT  TOP (1) O_ORDERKEY,
	    O_CUSTKEY,
	    O_ORDERSTATUS,
	    O_TOTALPRICE,
	    O_ORDERDATE,
	    O_ORDERPRIORITY,
	    O_CLERK,
	    O_SHIPPRIORITY,
	    O_COMMENT 
FROM    ORDERS
ORDER   BY O_ORDERKEY     ASC

PRINT   ' '
PRINT   'Maximum Primary Key ID From ORDERS'
SELECT  TOP (1) O_ORDERKEY,
	    O_CUSTKEY,
	    O_ORDERSTATUS,
	    O_TOTALPRICE,
	    O_ORDERDATE,
	    O_ORDERPRIORITY,
	    O_CLERK,
	    O_SHIPPRIORITY,
	    O_COMMENT 
FROM    ORDERS
ORDER   BY O_ORDERKEY     DESC

-----------------------
---     LINEITEM      ---
-----------------------
PRINT   ' '
PRINT   '***********************'
PRINT   '***     LINEITEM      ***'
PRINT   '***********************'
PRINT   ' '
SELECT  COUNT_BIG(*) AS 'Number of LINEITEM Rows'
FROM    LINEITEM

PRINT   ' '
PRINT   'Minimum Primary Key ID From LINEITEM'
SELECT  TOP (1) L_ORDERKEY,
	    L_PARTKEY,
	    L_SUPPKEY ,
	    L_LINENUMBER,
	    L_QUANTITY,
	    L_EXTENDEDPRICE,
	    L_DISCOUNT,
	    L_TAX,
	    L_RETURNFLAG,
	    L_LINESTATUS,
	    L_SHIPDATE ,
	    L_COMMITDATE,
	    L_RECEIPTDATE,
	    L_SHIPINSTRUCT,
	    L_SHIPMODE,
	    L_COMMENT
FROM    LINEITEM
ORDER   BY L_ORDERKEY ASC

PRINT   ' '
PRINT   'Maximum Primary Key ID From LINEITEM'
SELECT  TOP (1) L_ORDERKEY,
	    L_PARTKEY,
	    L_SUPPKEY ,
	    L_LINENUMBER,
	    L_QUANTITY,
	    L_EXTENDEDPRICE,
	    L_DISCOUNT,
	    L_TAX,
	    L_RETURNFLAG,
	    L_LINESTATUS,
	    L_SHIPDATE ,
	    L_COMMITDATE,
	    L_RECEIPTDATE,
	    L_SHIPINSTRUCT,
	    L_SHIPMODE,
	    L_COMMENT
FROM    LINEITEM
ORDER   BY L_ORDERKEY DESC

PRINT   ' '
PRINT   'TPC-H Tables End'
SELECT  CONVERT(CHAR(30), GETDATE(), 21)
PRINT   ' '
PRINT   ''
PRINT   '---------------------------------'
PRINT   '|  DB Primary Key Check - End   |'
PRINT   '---------------------------------'
PRINT   ' '
SELECT  CONVERT(CHAR(30), GETDATE(), 21)
GO

-- set the MAX Degree of Parallelism to 1 for this runtime
-- sp_configure    'max degree of parallelism', 160
--sp_configure    'max degree of parallelism', 60
--GO

-- reconfigure the server so the MAXDOP will take effect
--RECONFIGURE WITH OVERRIDE
--GO

