CREATE DATABASE TPCHdestination ONPRIMARY( NAME = N'TPCHdata0',FILENAME = N'C:\Mount\Drive1\SQLdata\TPCHdata0.mdf' ,SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% ),FILEGROUP FG1( NAME = N'TPCHdataG1F1',FILENAME = N'C:\Mount\Drive1\SQLdata\TPCHdataG1F1.mdf' ,SIZE = 24GB , MAXSIZE = 24GB ),FILEGROUP FG2( NAME = N'TPCHdataG2F1',FILENAME = N'C:\Mount\Drive2\SQLdata\TPCHdataG2F1.mdf' ,SIZE = 24GB , MAXSIZE = 24GB ),. . .LOG ON( NAME = N'TPCHdata_log',FILENAME = N'C:\Mount\Log\SQLlog\TPCHdata_log.ldf' ,SIZE = 25GB , MAXSIZE = 25GB )GOALTER DATABASE TPCHdestination SET RECOVERY SIMPLEGOsp_dboption 'TPCHdestination','auto create statistics','OFF' ;gosp_dboption 'TPCHdestination','auto update statistics','OFF' ;goalter database TPCHdestination set PAGE_VERIFY NONE ;goWith the file groups created, tables to be loaded with data were placed in the file groups, for example,create table ORDERS_6(O_ORDERDATE smalldatetimenot null,O_ORDERKEY bigint not null,O_CUSTKEY int not null,O_ORDERPRIORITY char(15) not null,O_SHIPPRIORITY int not null,O_CLERK char(15) not null,O_ORDERSTATUS char(1) not null,O_TOTALPRICEmoney not null,O_COMMENT varchar(79) not null)on FG6
CREATE PARTITION FUNCTION pfnORDER (bigint) AS RANGE LEFT FOR VALUES (107142850,214285700,321428550,428571424,535714274,642857124,749999974,857142848,964285698,1071428548,1178571398,1285714272,1392857122,1499999972,1607142822,1714285696,1821428546,1928571396,2035714246,2142857120,2249999970,2357142820,2464285670,2571428544,2678571394,2785714244,2892857094,2999999968,3107142818,3214285668,3321428518,3428571392,3535714242,3642857092,3749999942,3857142816,3964285666,4071428516,4178571366,4285714240,4392857090,4499999940,4607142790,4714285664,4821428514,4928571364,5035714214,5142857088,5249999938,5357142788,5464285638,5571428512,5678571362,5785714212,5892857062) -- maximum is 6000000000CREATE PARTITION SCHEME pscORDER AS PARTITION pfnORDER TO (FG1, FG2, FG3, FG4, FG5, FG6, FG7, FG8, FG9, FG10, FG11, FG12,FG13, FG14, FG15, FG16, FG17, FG18, FG19, FG20, FG21, FG22, FG23,FG24, FG25, FG26, FG27, FG28, FG29, FG30, FG31, FG32, FG33, FG34,FG35, FG36, FG37, FG38, FG39, FG40, FG41, FG42, FG43, FG44, FG45,FG46, FG47, FG48, FG49, FG50, FG51, FG52, FG53, FG54, FG55, FG56)GOcreate table ORDERS(O_ORDERDATE smalldatetimenot null,O_ORDERKEY bigint not null,O_CUSTKEY int not null,O_ORDERPRIORITY char(15) not null,O_SHIPPRIORITY int not null,O_CLERK char(15) not null,O_ORDERSTATUS char(1) not null,O_TOTALPRICEmoney not null,O_COMMENT varchar(79) not null)on pscORDER(O_ORDERKEY)
ALTER TABLE ORDERS_6 WITH CHECK ADD CONSTRAINT check_ORDERS_6CHECK (O_ORDERKEY >= 535714275 AND O_ORDERKEY <= 642857124)
ALTER TABLE ORDERS_6 SWITCH TO ORDERS PARTITION 6