01_Create_And_Prepare_Database.sql

/*************************************************************************************************
RUN THE ENTIRE SCRIPT AGAINST YOUR AZURE DWH INSTANCE
**************************************************************************************************/

PRINT 'START'
PRINT ''
PRINT 'CREATING DimProducts'
GO

CREATE TABLE DimProducts
WITH
(
	DISTRIBUTION = REPLICATE
	,CLUSTERED INDEX(idProduct)
)
AS
WITH Cte as (Select 1 id Union all Select 1)
 ,Cte4 as (Select A.id from Cte A, Cte B)
 ,Cte16 as (Select A.id from Cte4 A, Cte4 B)
 ,Cte256 as (Select A.id from Cte16 A, Cte16 B)
 ,Cte65536 as (Select A.id from Cte256 A, Cte256 B)
 ,Results as (Select -32769 + Row_Number() Over(Order by id)id From Cte65536)
SELECT TOP 500 CAST(id AS SMALLINT) idProduct ,'Product ' + cast(id as varchar(6)) ProdDescription from Results
--ORDER BY NEWID()
ORDER BY idProduct--NEWID()
GO

PRINT ''
PRINT 'DimProducts... Done'
GO

PRINT ''
PRINT 'CREATING CCI_DimProducts'
GO

CREATE TABLE CCI_DimProducts
WITH
(
	DISTRIBUTION = REPLICATE
	,CLUSTERED COLUMNSTORE INDEX
)
AS
WITH Cte as (Select 1 id Union all Select 1)
 ,Cte4 as (Select A.id from Cte A, Cte B)
 ,Cte16 as (Select A.id from Cte4 A, Cte4 B)
 ,Cte256 as (Select A.id from Cte16 A, Cte16 B)
 ,Cte65536 as (Select A.id from Cte256 A, Cte256 B)
 ,Results as (Select -32769 + Row_Number() Over(Order by A.id)id From Cte65536 A,Cte256 B)
SELECT TOP 10000000 CAST(id AS INT) idProduct 
			,'Product name ' + cast(id as varchar(25)) ProdName
			--, 'Product description ' + cast(id as varchar(25)) ProductDetails 
FROM Results
GO

PRINT ''
PRINT 'CCI_DimProducts... Done'
GO

PRINT ''
PRINT 'CREATING RS_DimProducts'
GO

CREATE TABLE RS_DimProducts
WITH
(
	DISTRIBUTION = REPLICATE
	,CLUSTERED INDEX(idProduct)
)
AS
WITH Cte as (Select 1 id Union all Select 1)
 ,Cte4 as (Select A.id from Cte A, Cte B)
 ,Cte16 as (Select A.id from Cte4 A, Cte4 B)
 ,Cte256 as (Select A.id from Cte16 A, Cte16 B)
 ,Cte65536 as (Select A.id from Cte256 A, Cte256 B)
 ,Results as (Select -32769 + Row_Number() Over(Order by A.id)id From Cte65536 A,Cte256 B)
SELECT TOP 10000000 CAST(id AS INT) idProduct 
			,'Product name ' + cast(id as varchar(25)) ProdName
			--, 'Product description ' + cast(id as varchar(25)) ProductDetails
FROM Results
GO

PRINT ''
PRINT 'RS_DimProducts... Done'
GO

PRINT ''
PRINT 'CREATING DimSellers'
GO

CREATE TABLE DimSellers
WITH
(
	DISTRIBUTION = REPLICATE
	,CLUSTERED INDEX(idSeller)
)
AS
WITH Cte as (Select 1 id Union all Select 1)
 ,Cte4 as (Select A.id from Cte A, Cte B)
 ,Cte16 as (Select A.id from Cte4 A, Cte4 B)
 ,Cte256 as (Select A.id from Cte16 A, Cte16 B)
 ,Cte65536 as (Select A.id from Cte256 A, Cte256 B)
 ,Results as (Select -32769 + Row_Number() Over(Order by id)id From Cte65536)
SELECT TOP 500 CAST(id AS SMALLINT) idSeller ,'Seller ' + cast(id as varchar(6)) Surname from Results
--ORDER BY NEWID()
ORDER BY idseller--NEWID()
GO
 
PRINT ''
PRINT 'DimSellers... Done'
GO

PRINT ''
PRINT 'CREATING DimTerritories'
GO

CREATE TABLE DimTerritories
WITH
(
	DISTRIBUTION = REPLICATE
	,CLUSTERED INDEX(idTerritory)
)
AS
WITH Cte as (Select 1 id Union all Select 1)
 ,Cte4 as (Select A.id from Cte A, Cte B)
 ,Cte16 as (Select A.id from Cte4 A, Cte4 B)
 ,Cte256 as (Select A.id from Cte16 A, Cte16 B)
 ,Results as (Select Row_Number() Over(Order by id)id From Cte256)
SELECT CAST(id AS TINYINT) idTerritory ,'Territory ' + cast(id as varchar(6)) [Name] from Results
WHERE id <= 255
GO

PRINT ''
PRINT 'DimTerritories... Done'
GO

PRINT ''
PRINT 'CREATING CCI_FactSales_FewRows'
GO

CREATE TABLE CCI_FactSales_FewRows
WITH
(
	DISTRIBUTION = HASH(id)
	,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT TOP 4000000 * 
	,ABS(CAST(NEWID() AS binary(6)) %1000) + 1 Qty
	,ABS(CAST(NEWID() AS binary(6)) %1000) + 1 Amount
FROM
(
	SELECT 
		 ROW_NUMBER() OVER(ORDER BY A.idProduct) id
		,C.idSeller
		,A.idProduct
		,B.idTerritory
	FROM DimProducts A, DimTerritories B, DimSellers C
) [ROWS]
GO

PRINT ''
PRINT 'CCI_FactSales_FewRows... Done'
GO

PRINT ''
PRINT 'CREATING RS_FactSales_FewRows'
GO

CREATE TABLE RS_FactSales_FewRows
WITH
(
	DISTRIBUTION = HASH(id)
	,CLUSTERED INDEX(id)
)
AS
SELECT * FROM CCI_FactSales_FewRows
GO

PRINT ''
PRINT 'RS_FactSales_FewRows... Done'
GO

PRINT ''
PRINT 'CREATING CCI_FactSales'
GO

CREATE TABLE CCI_FactSales
WITH
(
	DISTRIBUTION = HASH(id)
	,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT * 
	,ABS(CAST(NEWID() AS binary(6)) %1000) + 1 Qty
	,ABS(CAST(NEWID() AS binary(6)) %1000) + 1 Amount
FROM
(
	SELECT 
		 ROW_NUMBER() OVER(ORDER BY A.idProduct) id
		,C.idSeller
		,A.idProduct
		,B.idTerritory
	FROM DimProducts A, DimTerritories B, DimSellers C
) [ROWS]
GO

PRINT ''
PRINT 'CCI_FactSales... Done'
GO

PRINT ''
PRINT 'CREATING RS_FactSales'
GO

CREATE TABLE RS_FactSales
WITH
(
	DISTRIBUTION = HASH(id)
	,CLUSTERED INDEX(id)
)
AS
SELECT * FROM CCI_FactSales
GO

PRINT ''
PRINT 'RS_FactSales... Done'
GO

PRINT ''
PRINT 'CREATING CCI_FactInternetSales'
GO

CREATE TABLE CCI_FactInternetSales
WITH
(
	DISTRIBUTION = HASH(idProduct)
	,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT * FROM CCI_FactSales
GO

PRINT ''
PRINT 'CCI_FactInternetSales... Done'
GO

PRINT ''
PRINT 'CREATING STATISTICS ON CCI_FactInternetSales'
GO

CREATE STATISTICS st1 ON CCI_FactInternetSales (id)  
CREATE STATISTICS st2 ON CCI_FactInternetSales (idproduct)  
CREATE STATISTICS st3 ON CCI_FactInternetSales (idterritory)
CREATE STATISTICS st4 ON CCI_FactInternetSales (qty)
CREATE STATISTICS st5 ON CCI_FactInternetSales (amount)
GO

PRINT ''
PRINT 'STATISTICS ON CCI_FactInternetSales... Done'
GO

PRINT ''
PRINT 'CREATING STATISTICS ON CCI_FactSales'
GO

CREATE STATISTICS st1 ON CCI_FactSales (id)  
CREATE STATISTICS st2 ON CCI_FactSales (idproduct)  
CREATE STATISTICS st3 ON CCI_FactSales (idterritory)
CREATE STATISTICS st4 ON CCI_FactSales (qty)
CREATE STATISTICS st5 ON CCI_FactSales (amount)
GO

PRINT ''
PRINT 'STATISTICS ON CCI_FactSales... Done'
GO

PRINT ''
PRINT 'CREATING CCI_FactSales_WritesImpact'
GO

CREATE TABLE CCI_FactSales_WritesImpact
WITH
(
	DISTRIBUTION = HASH(id)
	,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT * FROM CCI_FactSales
UNION ALL
SELECT * FROM CCI_FactSales
GO

PRINT ''
PRINT 'CCI_FactSales_WritesImpact... Done'
GO

PRINT ''
PRINT 'CREATING CCI_FactSales_OverPartitioned'
GO

CREATE TABLE CCI_FactSales_OverPartitioned
WITH
(
	DISTRIBUTION = HASH(id)
	,CLUSTERED COLUMNSTORE INDEX
	,PARTITION ( id RANGE RIGHT FOR VALUES ( 1,100000,127501,191251,510001,573751,637501,701251,1020001,1083751,1147501,1211251,1593751,1657501,1721251,1785001,2103751,2167501,2231251,2295001,2613751,2677501,2741251,2805001,3187501,3251251,
											3315001,3378751,3697501,3761251,3825001,3888751,4207501,4271251,4335001,4398751,4462501,4781251,4845001,4908751,4972501,5291251,5355001,5418751,5482501,5801251,5865001,5928751,5992501,6056251,6375001,6438751,6502501,6566251,6885001,6948751,
											7012501,7076251,7395001,7458751,7522501,7586251,7650001,7968751,8032501,8096251,8160001,8478751,8542501,8606251,8670001,9052501,9116251,9180001,9243751,9562501,9626251,9690001,9753751,10072501,10136251,10200001,10263751,10646251,10710001,
											10773751,10837501,11156251,11220001,11283751,11347501,11666251,11730001,11793751,11857501,11921251,12240001,12303751,12367501,12431251,12750001,12813751,12877501,12941251,13260001,13323751,13387501,13451251,13515001,13833751,13897501,13961251,
											14025001,14343751,14407501,14471251,14535001,14853751,14917501,14981251,15045001,15108751,15427501,15491251,15555001,15618751,15937501,16001251,16065001,16128751,16511251,16575001,16638751,16702501,17021251,17085001,17148751,17212501,17531251,
											17595001,17658751,17722501,18105001,18168751,18232501,18296251,18615001,18678751,18742501,18806251,19125001,19188751,19252501,19316251,19380001,19698751,19762501,19826251,19890001,20208751,20272501,20336251,20400001,20718751,20782501,20846251,
											20910001,20973751,21292501,21356251,21420001,21483751,21802501,21866251,21930001,21993751,22312501,22376251,22440001,22503751,22567501,22886251,22950001,23013751,23077501,23396251,23460001,23523751,23587501,23970001,24033751,24097501,24161251,
											24480001,24543751,24607501,24671251,24990001,25053751,25117501,25181251,25563751,25627501,25691251,25755001,26073751,26137501,26201251,26265001,26583751,26647501,26711251,26775001,27157501,27221251,27285001,27348751,27667501,27731251,27795001,
											27858751,28177501,28241251,28305001,28368751,28432501,28751251,28815001,28878751,28942501,29261251,29325001,29388751,29452501,29771251,29835001,29898751,29962501,30026251,30345001,30408751,30472501,30536251,30855001,30918751,30982501,31046251,
											31428751,31492501,31556251,31620001,31938751,32002501,32066251,32130001,32448751,32512501,32576251,32640001,33022501,33086251,33150001,33213751,33532501,33596251,33660001,33723751,34042501,34106251,34170001,34233751,34616251,34680001,34743751,
											34807501,35126251,35190001,35253751,35317501,35636251,35700001,35763751,35827501,35891251,36210001,36273751,36337501,36401251,36720001,36783751,36847501,36911251,37230001,37293751,37357501,37421251,37485001,37803751,37867501,37931251,37995001,
											38313751,38377501,38441251,38505001,38887501,38951251,39015001,39078751,39397501,39461251,39525001,39588751,39907501,39971251,40035001,40098751,40481251,40545001,40608751,40672501,40991251,41055001,41118751,41182501,41501251,41565001,41628751,
											41692501,42075001,42138751,42202501,42266251,42585001,42648751,42712501,42776251,43095001,43158751,43222501,43286251,43350001,43668751,43732501,43796251,43860001,44178751,44242501,44306251,44370001,44688751,44752501,44816251,44880001,44943751,
											45262501,45326251,45390001,45453751,45772501,45836251,45900001,45963751,46282501,46346251,46410001,46473751,46537501,46856251,46920001,46983751,47047501,47366251,47430001,47493751,47557501,47940001,48003751,48067501,48131251,48450001,48513751,
											48577501,48641251,48960001,49023751,49087501,49151251,49533751,49597501,49661251,49725001,50043751,50107501,50171251,50235001,50553751,50617501,50681251,50745001,50808751,51127501,51191251,51255001,51318751,51637501,51701251,51765001,255001,318751,
											382501,446251,765001,828751,892501,1000000,1275001,1338751,1402501,1466251,1530001,1848751,1912501,1976251,2040001,2358751,2422501,2486251,2550001,2868751,2932501,2996251,3060001,3123751,3442501,3506251,3570001,3633751,3952501,4016251,4080001,4143751,
											4526251,4590001,4653751,4717501,5036251,5100001,5163751,5227501,5546251,5610001,5673751,5737501,6120001,6183751,6247501,6311251,6630001,6693751,6757501,6821251,7140001,7203751,7267501,7331251,7713751,7777501,7841251,7905001,8223751,8287501,8351251,8415001,
											8733751,8797501,8861251,8925001,8988751,9307501,9371251,9435001,9498751,9817501,9881251,10000000,10008751,10327501,10391251,10455001,10518751,10582501,10901251,10965001,11028751,11092501,11411251,11475001,11538751,11602501,11985001,12048751,12112501,
											12176251,12495001,12558751,12622501,12686251,13005001,13068751,13132501,13196251,13578751,13642501,13706251,13770001,14088751,14152501,14216251,14280001,14598751,14662501,14726251,14790001,15172501,15236251,15300001,15363751,15682501,15746251,15810001,
											15873751,16192501,16256251,16320001,16383751,16447501,16766251,16830001,16893751,16957501,17276251,17340001,17403751,17467501,17786251,17850001,17913751,17977501,18041251,18360001,18423751,18487501,18551251,18870001,18933751,18997501,19061251,19443751,
											19507501,19571251,19635001,19953751,20017501,20081251,20145001,20463751,20527501,20591251,20655001,21037501,21101251,21165001,21228751,21547501,21611251,21675001,21738751,22057501,22121251,22185001,22248751,22631251,22695001,22758751,22822501,23141251,
											23205001,23268751,23332501,23651251,23715001,23778751,23842501,23906251,24225001,24288751,24352501,24416251,24735001,24798751,24862501,24926251,25245001,25308751,25372501,25436251,25500001,25818751,25882501,25946251,26010001,26328751,26392501,26456251,
											26520001,26838751,26902501,26966251,27030001,27093751,27412501,27476251,27540001,27603751,27922501,27986251,28050001,28113751,28496251,28560001,28623751,28687501,29006251,29070001,29133751,29197501,29516251,29580001,29643751,29707501,30090001,30153751,
											30217501,30281251,30600001,30663751,30727501,30791251,31110001,31173751,31237501,31301251,31365001,31683751,31747501,31811251,31875001,32193751,32257501,32321251,32385001,32703751,32767501,32831251,32895001,32958751,33277501,33341251,33405001,33468751,
											33787501,33851251,33915001,33978751,34297501,34361251,34425001,34488751,34552501,34871251,34935001,34998751,35062501,35381251,35445001,35508751,35572501,35955001,36018751,36082501,36146251,36465001,36528751,36592501,36656251,36975001,37038751,37102501,37166251,
											37548751,37612501,37676251,37740001,38058751,38122501,38186251,38250001,38568751,38632501,38696251,38760001,38823751,39142501,39206251,39270001,39333751,39652501,39716251,39780001,39843751,40162501,40226251,40290001,40353751,40417501,40736251,40800001,
											40863751,40927501,41246251,41310001,41373751,41437501,41756251,41820001,41883751,41947501,42011251,42330001,42393751,42457501,42521251,42840001,42903751,42967501,43031251,43413751,43477501,43541251,43605001,43923751,43987501,44051251,44115001,44433751,
											44497501,44561251,44625001,45007501,45071251,45135001,45198751,45517501,45581251,45645001,45708751,46027501,46091251,46155001,46218751,46601251,46665001,46728751,46792501,47111251,47175001,47238751,47302501,47621251,47685001,47748751,47812501,47876251,
											48195001,48258751,48322501,48386251,48705001,48768751,48832501,48896251,49215001,49278751,49342501,49406251,49470001,49788751,49852501,49916251,49980001,50298751,50362501,50426251,50490001,50872501,50936251,51000001,51063751,51382501,51446251,51510001,
											51573751,51892501,51956251,52020001,52083751,52466251,52530001,52593751,52657501,52976251,53040001,53103751,53167501,53486251,53550001,53613751,53677501,54060001,54123751,54187501,54251251,54570001,54633751,54697501,54761251,55080001,55143751,55207501,
											55271251,55335001,55653751,55717501,55781251,55845001,56163751,56227501,56291251,56355001,56673751,56737501,56801251,56865001,56928751,57247501,57311251,57375001,57438751,57757501,57821251,57885001,57948751,58331251,58395001,58458751,58522501,58841251,
											58905001,58968751,59032501,59351251,59415001,59478751,59542501,59925001,59988751,60052501,60116251,60435001,60498751,60562501,60626251,60945001,61008751,61072501,61136251,61518751,61582501,61646251,61710001,62028751,62092501,62156251,62220001,62538751,
											62602501,62666251,62730001,62793751,63112501,63176251,63240001,63303751,63622501,63686251,51828751,52147501,52211251,52275001,52338751,52402501,52721251,52785001,52848751,52912501,53231251,53295001,53358751,53422501,53741251,53805001,53868751,53932501,
											53996251,54315001,54378751,54442501,54506251,54825001,54888751,54952501,55016251,55398751,55462501,55526251,55590001,55908751,55972501,56036251,56100001,56418751,56482501,56546251,56610001,56992501,57056251,57120001,57183751,57502501,57566251,57630001,
											57693751,58012501,58076251,58140001,58203751,58267501,58586251,58650001,58713751,58777501,59096251,59160001,59223751,59287501,59606251,59670001,59733751,59797501,59861251,60180001,60243751,60307501,60371251,60690001,60753751,60817501,60881251,61200001,
											61263751,61327501,61391251,61455001,61773751,61837501,61901251,61965001,62283751,62347501,62411251,62475001,62857501,62921251,62985001,63048751,63367501,63431251,63495001,63558751 ) )  )
AS
SELECT * FROM CCI_FactSales
GO

PRINT ''
PRINT 'CCI_FactSales_OverPartitioned... Done'
GO

PRINT ''
PRINT 'CREATING CCI_FactSales_Non_Skewed'
GO

CREATE TABLE CCI_FactSales_Non_Skewed
WITH
(
	DISTRIBUTION = HASH(id)
	,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT * FROM CCI_FactSales_WritesImpact
GO

PRINT ''
PRINT 'CCI_FactSales_Non_Skewed... Done'
GO

PRINT ''
PRINT 'CREATING CCI_FactSales_Skewed'
GO

CREATE TABLE CCI_FactSales_Skewed
WITH
(
	DISTRIBUTION = HASH(id)
	,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT 
		135154 id
		,idSeller
		,idProduct
		,idTerritory
		,Qty
		,Amount
FROM CCI_FactSales 
UNION ALL
SELECT * FROM CCI_FactSales
GO

PRINT ''
PRINT 'CCI_FactSales_Skewed... Done'
GO

PRINT ''
PRINT 'COMPLETED '
GO