-----------------------------------------------------------------------------
-- Tony Andrews Benchmark 2  08 Dec 10 PerformanceDBA
--     DDL for Tables Product, Status, ProductStatus x 3 versions
--     Physical Stats
-----------------------------------------------------------------------------

create table Product (
	ProductId                       int                              not null  ,
	Description                     char(30)                         not null  ,
	Padding                         char(160)                        not null   
	)
    lock allpages
    on 'ref_seg'
create unique clustered index UC_ProductId 
    on Product(ProductId)
    on 'ref_seg'

create table Status (
	StatusCode                      tinyint                          not null  ,
	Name                            char(12)                         not null   
	)
    lock allpages
    on 'ref_seg'
create unique clustered index UC_StatusCode 
    on Status(StatusCode)
    on 'ref_seg'

create table ProductStatus (
	ProductId                       int                              not null  ,
	DateTime                        datetime                         not null  ,
	StatusCode                      tinyint                          not null   
)
lock allpages
    on 'data_seg'
--drop index ProductStatus.UC_Prod_Dtm
create unique clustered index UC_Prod_Dtm 
    on ProductStatus(ProductId, DateTime)
    --with sorted_data
    on 'data_seg'

create table ProductStatusBit (
	ProductId                       int                              not null  ,
	DateTime                        datetime                         not null  ,
	IsCurrent                       bit                              not null  ,
	StatusCode                      tinyint                          not null   
)
lock allpages
    on 'parttn_seg'
--drop index ProductStatusBit.UC_Prod_Dtm
create unique clustered index UC_Prod_Dtm 
    on ProductStatusBit(ProductId, DateTime)
    --with sorted_data
    on 'parttn_seg'

create table ProductStatusDupe (
	ProductId                       int                              not null  ,
	StartDateTime                   datetime                         not null  ,
	EndDateTime                     datetime                         not null  ,
	StatusCode                      tinyint                          not null   
)
lock allpages
    on 'parttn_seg'
-- drop index ProductStatusDupe.UC_Prod_Dtm
create unique clustered index UC_Prod_Dtm 
    on ProductStatusDupe(ProductId, StartDateTime)
    --with sorted_data
    on 'parttn_seg'

INSERT Status VALUES (0, "Current")
INSERT Status VALUES (1, "Recent")
INSERT Status VALUES (2, "Dated")
INSERT Status VALUES (3, "Old")
INSERT Status VALUES (4, "Dark Ages")
INSERT Status VALUES (5, "Ancient")
INSERT Status VALUES (6, "Paleolithic")
INSERT Status VALUES (7, "Cretaceous")
INSERT Status VALUES (8, "Saurischian")
INSERT Status VALUES (9, "Obsolete")

-----------------------------------
--  Physical Stats after loading --
-----------------------------------

--  Product

    index_name   size  reserved unused
    ------------ ----- -------- ------
    UC_ProductId 38 KB 4054 KB  16 KB
    name    rowtotal reserved data    index_size unused
    ------- -------- -------- ------- ---------- ------
    Product 20000    4054 KB  4000 KB 38 KB      16 KB

--  ProductStatus

    index_name  size   reserved unused
    ----------- ------ -------- ------
    UC_Prod_Dtm 594 KB 34506 KB 12 KB

    name          rowtotal reserved data     index_size unused
    ------------- -------- -------- -------- ---------- ------
    ProductStatus 2000000  34506 KB 33900 KB 594 KB     12 KB

--  ProductStatusBit


    index_name  size   reserved unused
    ----------- ------ -------- ------
    UC_Prod_Dtm 626 KB 36370 KB 28 KB

    name             rowtotal reserved data     index_size unused
    ---------------- -------- -------- -------- ---------- ------
    ProductStatusBit 2000000  36370 KB 35716 KB 626 KB     28 KB

--  ProductStatusDupe

    index_name  size   reserved unused
    ----------- ------ -------- ------
    UC_Prod_Dtm 878 KB 50904 KB 26 KB

    name              rowtotal reserved data     index_size unused
    ----------------- -------- -------- -------- ---------- ------
    ProductStatusDupe 2000000  50904 KB 50000 KB 878 KB     26 KB