Database Reference
In-Depth Information
Quantity decimal(9,3) not null,
UnitPrice money not null,
Amount money not null,
DiscountPcnt decimal (6,3) not null,
DiscountAmt money not null,
TaxAmt money not null,
primary key (DateId, ArticleId, BranchId, OrderId)
with (data_compression = page)
);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N2 as T1 cross join N4 as T2) -- 1,024 rows
,IDs(ID) as (select ROW_NUMBER() over (order by (select NULL)) from N5)
,Dates(DateId, ADate)
as
(
select ID, dateadd(day,ID,'2012-12-31')
from IDs
where ID <= 727
)
insert into dbo.DimDates(DateId, ADate, ADay, AMonth, AnYear
,AQuarter, ADayOfWeek)
select DateID, ADate, Day(ADate), Month(ADate), Year(ADate)
,datepart(qq,ADate), datepart(dw,ADate)
from Dates;
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,IDs(ID) as (select ROW_NUMBER() over (order by (select NULL)) from N3)
insert into dbo.DimBranches(BranchId, BranchNumber, BranchCity,
BranchRegion, BranchCountry)
select ID, convert(nvarchar(32),ID), 'City', 'Region', 'Country'
from IDs
where ID <= 13;
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N2 as T2) -- 1,024 rows
,IDs(ID) as (select ROW_NUMBER() over (order by (select NULL)) from N5)
insert into dbo.DimArticles(ArticleId, ArticleCode, ArticleCategory)
select ID, convert(nvarchar(32),ID), 'Category ' + convert(nvarchar(32),ID % 51)
from IDs
where ID <= 1021;
