· 6 years ago · Jan 19, 2020, 02:54 AM
1/*============================================================================
2 File: instawdwdb.sql
3
4 Summary: Creates the AdventureWorks 2008R2 data warehouse sample database.
5
6 Date: June 14, 2008
7 Updated: March 28, 2012
8============================================================================*/
9
10-->> WARNING: THIS SCRIPT MUST BE RUN IN SQLCMD MODE INSIDE SQL SERVER MANAGEMENT STUDIO. <<--
11:on error exit
12
13/*
14 * In order to run this script manually, either set the environment variables,
15 * or uncomment the setvar statements and provide the necessary values if
16 * the defaults are not correct for your installation.
17 */
18
19
20:setvar SqlSamplesDatabasePath "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\"
21:setvar SqlSamplesSourceDataPath "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorksData Warehouse\KAMIL\"
22
23IF '$(SqlSamplesSourceDataPath)' IS NULL OR '$(SqlSamplesSourceDataPath)' = ''
24BEGIN
25 RAISERROR(N'The variable SqlSamplesSourceDataPath must be defined.', 16, 127) WITH NOWAIT;
26 RETURN;
27END;
28
29IF '$(SqlSamplesDatabasePath)' IS NULL OR '$(SqlSamplesDatabasePath)' = ''
30BEGIN
31 RAISERROR(N'The variable SqlSamplesDatabasePath must be defined.', 16, 127) WITH NOWAIT;
32 RETURN;
33END;
34
35SET NOCOUNT OFF;
36GO
37
38PRINT CONVERT(VARCHAR(1000), @@VERSION);
39GO
40
41USE [master];
42GO
43
44SET QUOTED_IDENTIFIER ON;
45
46-- ****************************************
47-- Drop Database
48-- ****************************************
49PRINT '';
50PRINT '*** Dropping Database';
51GO
52
53IF EXISTS (SELECT [name] FROM [master].[sys].[databases] WHERE [name] = N'AdventureKamil')
54 DROP DATABASE [AdventureKamil];
55GO
56
57-- ****************************************
58-- Create Database
59-- ****************************************
60PRINT '';
61PRINT '*** Creating Database';
62GO
63
64CREATE DATABASE [AdventureKamil] ON (NAME = 'AdventureKamil_Data',
65 FILENAME = N'$(SqlSamplesDatabasePath)AdventureKamil_Data.mdf', SIZE = 64, FILEGROWTH = 4) LOG ON (NAME = 'AdventureKamil_Log',
66 FILENAME = N'$(SqlSamplesDatabasePath)AdventureKamil_Log.LDF' , SIZE = 2, FILEGROWTH = 8);
67GO
68
69ALTER DATABASE AdventureKamil
70SET RECOVERY SIMPLE,
71 ANSI_NULLS ON,
72 ANSI_PADDING ON,
73 ANSI_WARNINGS ON,
74 ARITHABORT ON,
75 CONCAT_NULL_YIELDS_NULL ON,
76 QUOTED_IDENTIFIER ON,
77 NUMERIC_ROUNDABORT OFF,
78 PAGE_VERIFY CHECKSUM,
79 ALLOW_SNAPSHOT_ISOLATION ON;
80GO
81
82USE [AdventureKamil];
83GO
84
85
86-- ******************************************************
87-- Create tables
88-- ******************************************************
89PRINT '';
90PRINT '*** Creating Tables';
91GO
92
93CREATE TABLE [dbo].[DimCustomer] (
94 [CustomerKey] [int] IDENTITY(1, 1) NOT NULL,
95 [GeographyKey] [int] NULL,
96 [CustomerAlternateKey] [nvarchar] (15) NOT NULL,
97 [Title] [nvarchar] (8) NULL,
98 [FirstName] [nvarchar] (50) NULL,
99 [MiddleName] [nvarchar] (50) NULL,
100 [LastName] [nvarchar] (50) NULL,
101 [NameStyle] [bit] NULL,
102 [BirthDate] [date] NULL,
103 [MaritalStatus] [nchar] (1) NULL,
104 [Suffix] [nvarchar] (10) NULL,
105 [Gender] [nvarchar] (1) NULL,
106 [EmailAddress] [nvarchar] (50) NULL,
107 [YearlyIncome] [money] NULL,
108 [TotalChildren] [tinyint] NULL,
109 [NumberChildrenAtHome] [tinyint] NULL,
110 [EnglishEducation] [nvarchar] (40) NULL,
111 [SpanishEducation] [nvarchar] (40) NULL,
112 [FrenchEducation] [nvarchar] (40) NULL,
113 [EnglishOccupation] [nvarchar] (100) NULL,
114 [SpanishOccupation] [nvarchar] (100) NULL,
115 [FrenchOccupation] [nvarchar] (100) NULL,
116 [HouseOwnerFlag] [nchar] (1) NULL,
117 [NumberCarsOwned] [tinyint] NULL,
118 [AddressLine1] [nvarchar] (120) NULL,
119 [AddressLine2] [nvarchar] (120) NULL,
120 [Phone] [nvarchar] (20) NULL,
121 [DateFirstPurchase] [date] NULL,
122 [CommuteDistance] [nvarchar] (15) NULL
123) ON [PRIMARY];
124
125CREATE TABLE [dbo].[DimDate] (
126 [DateKey] int NOT NULL,
127 [FullDateAlternateKey] [date] NOT NULL,
128 [DayNumberOfWeek] [tinyint] NOT NULL,
129 [EnglishDayNameOfWeek] [nvarchar] (10) NOT NULL,
130 [SpanishDayNameOfWeek] [nvarchar] (10) NOT NULL,
131 [FrenchDayNameOfWeek] [nvarchar] (10) NOT NULL,
132 [DayNumberOfMonth] [tinyint] NOT NULL,
133 [DayNumberOfYear] [smallint] NOT NULL,
134 [WeekNumberOfYear] [tinyint] NOT NULL,
135 [EnglishMonthName] [nvarchar] (10) NOT NULL,
136 [SpanishMonthName] [nvarchar] (10) NOT NULL,
137 [FrenchMonthName] [nvarchar] (10) NOT NULL,
138 [MonthNumberOfYear] [tinyint] NOT NULL,
139 [CalendarQuarter] [tinyint] NOT NULL,
140 [CalendarYear] [smallint] NOT NULL,
141 [CalendarSemester] [tinyint] NOT NULL,
142 [FiscalQuarter] [tinyint] NOT NULL,
143 [FiscalYear] [smallint] NOT NULL,
144 [FiscalSemester] [tinyint] NOT NULL
145) ON [PRIMARY];
146
147CREATE TABLE [dbo].[DimSalesTerritory] (
148 [SalesTerritoryKey] [int] IDENTITY(1, 1) NOT NULL,
149 [SalesTerritoryAlternateKey] [int] NULL,
150 [SalesTerritoryRegion] [nvarchar] (50) NOT NULL,
151 [SalesTerritoryCountry] [nvarchar] (50) NOT NULL,
152 [SalesTerritoryGroup] [nvarchar] (50) NULL
153) ON [PRIMARY];
154
155CREATE TABLE [dbo].[FactRent] (
156 [RentKey] [int] IDENTITY(1, 1) NOT NULL,
157 [RoomKey] [int] NOT NULL,
158 [CustomerKey] [int] NOT NULL,
159 [OrderDateKey] [int] NOT NULL,
160 [RentStartKey] [int] NOT NULL,
161 [Length] [int] NOT NULL,
162 [Price] [money] NOT NULL,
163) ON [PRIMARY];
164
165CREATE TABLE [dbo].[DimGeography] (
166 [GeographyKey] [int] IDENTITY(1, 1) NOT NULL,
167 [City] [nvarchar] (30) NULL,
168 [StateProvinceCode] [nvarchar] (3) NULL,
169 [StateProvinceName] [nvarchar] (50) NULL,
170 [CountryRegionCode] [nvarchar] (3) NULL,
171 [EnglishCountryRegionName] [nvarchar] (50) NULL,
172 [SpanishCountryRegionName] [nvarchar] (50) NULL,
173 [FrenchCountryRegionName] [nvarchar] (50) NULL,
174 [PostalCode] [nvarchar] (15) NULL,
175 [SalesTerritoryKey] [int] NULL
176) ON [PRIMARY];
177
178CREATE TABLE [dbo].[DimRoom] (
179 [RoomKey] [int] IDENTITY(1, 1) NOT NULL,
180 [GeographyKey] [int] NULL,
181 [HaveTv] [bit] NOT NULL,
182 [HaveNet] [bit] NOT NULL,
183 [HaveWasher] [bit] NOT NULL,
184 [HaveCoffeeMachine] [bit] NOT NULL
185) ON [PRIMARY];
186
187GO
188
189-- ******************************************************
190-- Load data
191-- ******************************************************
192PRINT '';
193PRINT '*** Loading Data';
194GO
195
196PRINT 'Loading [AdventureKamil].[dbo].[DimCustomer]';
197
198BULK INSERT [AdventureKamil].[dbo].[DimCustomer] FROM N'$(SqlSamplesSourceDataPath)DimCustomer.csv'
199WITH (
200 CODEPAGE='ACP',
201 DATAFILETYPE = 'char',
202 FIELDTERMINATOR= '\t',
203 ROWTERMINATOR = '\n' ,
204 KEEPIDENTITY,
205 TABLOCK
206)
207
208PRINT 'Loading [AdventureKamil].[dbo].[DimData]';
209
210BULK INSERT [AdventureKamil].[dbo].[DimDate] FROM N'$(SqlSamplesSourceDataPath)DimDate.csv'
211WITH (
212 CODEPAGE='ACP',
213 DATAFILETYPE = 'char',
214 FIELDTERMINATOR= '\t',
215 ROWTERMINATOR = '\n' ,
216 KEEPIDENTITY,
217 TABLOCK
218)
219
220PRINT 'Loading [AdventureKamil].[dbo].[DimGeography]';
221
222BULK INSERT [AdventureKamil].[dbo].[DimGeography] FROM N'$(SqlSamplesSourceDataPath)DimGeography.csv'
223WITH (
224 CODEPAGE='ACP',
225 DATAFILETYPE = 'char',
226 FIELDTERMINATOR= '\t',
227 ROWTERMINATOR = '\n' ,
228 KEEPIDENTITY,
229 TABLOCK
230)
231
232
233
234PRINT 'Loading [AdventureKamil].[dbo].[FactRent]';
235
236BULK INSERT [AdventureKamil].[dbo].[FactRent] FROM N'$(SqlSamplesSourceDataPath)FactRent.csv'
237WITH (
238 CODEPAGE='ACP',
239 DATAFILETYPE = 'char',
240 FIELDTERMINATOR= '\t',
241 ROWTERMINATOR = '\n' ,
242 KEEPIDENTITY,
243 TABLOCK
244)
245
246PRINT 'Loading [AdventureKamil].[dbo].[DimRoom]';
247
248BULK INSERT [AdventureKamil].[dbo].[DimRoom] FROM N'$(SqlSamplesSourceDataPath)DimRoom.csv'
249WITH (
250 CODEPAGE='ACP',
251 DATAFILETYPE = 'char',
252 FIELDTERMINATOR= '\t',
253 ROWTERMINATOR = '\n' ,
254 KEEPIDENTITY,
255 TABLOCK
256)
257
258PRINT 'Loading [AdventureKamil].[dbo].[DimSalesTerritory]';
259
260BULK INSERT [AdventureKamil].[dbo].[DimSalesTerritory] FROM N'$(SqlSamplesSourceDataPath)DimSalesTerritory.csv'
261WITH (
262 CODEPAGE='ACP',
263 DATAFILETYPE = 'char',
264 FIELDTERMINATOR= '\t',
265 ROWTERMINATOR = '\n' ,
266 KEEPIDENTITY,
267 TABLOCK
268)
269
270-- GO
271
272-- ******************************************************
273-- Add Primary Keys
274-- ******************************************************
275PRINT '';
276PRINT '*** Adding Primary Keys';
277GO
278
279ALTER TABLE [dbo].[DimCustomer] WITH CHECK ADD
280 CONSTRAINT [PK_DimCustomer_CustomerKey] PRIMARY KEY CLUSTERED
281 (
282 [CustomerKey]
283 ) ON [PRIMARY];
284
285ALTER TABLE [dbo].[FactRent] WITH CHECK ADD
286 CONSTRAINT [PK_FactRent_RentKey] PRIMARY KEY CLUSTERED
287 (
288 [RentKey]
289 ) ON [PRIMARY];
290
291ALTER TABLE [dbo].[DimRoom] WITH CHECK ADD
292 CONSTRAINT [PK_DimRoom_RoomKey] PRIMARY KEY CLUSTERED
293 (
294 [RoomKey]
295 ) ON [PRIMARY];
296
297ALTER TABLE [dbo].[DimDate] WITH CHECK ADD
298 CONSTRAINT [PK_DimDate_DateKey] PRIMARY KEY CLUSTERED
299 (
300 [DateKey]
301 ) ON [PRIMARY];
302
303ALTER TABLE [dbo].[DimGeography] WITH CHECK ADD
304 CONSTRAINT [PK_DimGeography_GeographyKey] PRIMARY KEY CLUSTERED
305 (
306 [GeographyKey]
307 ) ON [PRIMARY];
308
309
310ALTER TABLE [dbo].[DimSalesTerritory] WITH CHECK ADD
311 CONSTRAINT [PK_DimSalesTerritory_SalesTerritoryKey] PRIMARY KEY CLUSTERED
312 (
313 [SalesTerritoryKey]
314 ) ON [PRIMARY];
315
316-- ****************************************
317-- Create Foreign key constraints
318-- ****************************************
319PRINT '';
320PRINT '*** Creating Foreign Key Constraints';
321GO
322
323ALTER TABLE [dbo].[DimCustomer] ADD
324 CONSTRAINT [FK_DimCustomer_DimGeography] FOREIGN KEY
325 (
326 [GeographyKey]
327 ) REFERENCES [dbo].[DimGeography] (
328 [GeographyKey]
329 );
330
331ALTER TABLE [dbo].[FactRent] ADD
332 CONSTRAINT [FK_FactRent_DimRoom] FOREIGN KEY
333 (
334 [RoomKey]
335 ) REFERENCES [dbo].[DimRoom] (
336 [RoomKey]
337 ),
338 CONSTRAINT [FK_FactRent_DimDate2] FOREIGN KEY
339 (
340 [RentStartKey]
341 ) REFERENCES [dbo].[DimDate] (
342 [DateKey]
343 );
344
345ALTER TABLE [dbo].[DimGeography] ADD
346 CONSTRAINT [FK_DimGeography_DimSalesTerritory] FOREIGN KEY
347 (
348 [SalesTerritoryKey]
349 ) REFERENCES [dbo].[DimSalesTerritory] (
350 [SalesTerritoryKey]
351 );
352
353ALTER TABLE [dbo].[DimRoom] ADD
354 CONSTRAINT [FK_DimRoom_DimGeography] FOREIGN KEY
355 (
356 [GeographyKey]
357 ) REFERENCES [dbo].[DimGeography] (
358 [GeographyKey]
359 );
360
361/*
362-- Output database object creation messages
363SELECT [PostTime], [DatabaseUser], [Event], [Schema], [Object], [TSQL], [XmlEvent]
364FROM [AdventureKamil].[dbo].[DatabaseLog];
365*/
366GO
367
368
369-- ****************************************
370-- Change File Growth Values for Database
371-- ****************************************
372PRINT '';
373PRINT '*** Changing File Growth Values for Database';
374GO
375
376ALTER DATABASE [AdventureKamil]
377MODIFY FILE (NAME = 'AdventureKamil_Data', FILEGROWTH = 16);
378ALTER DATABASE [AdventureKamil]
379MODIFY FILE (NAME = 'AdventureKamil_Log', FILEGROWTH = 16);
380GO
381
382
383-- ****************************************
384-- Shrink Database
385-- ****************************************
386PRINT '';
387PRINT '*** Shrinking Database';
388GO
389
390DBCC SHRINKDATABASE ([AdventureKamil]);
391GO
392
393USE [master]
394GO