I thought that someone out there who didn’t attend my session might want to use it, so here you are.
#ADVENTUREWORKS2012 DOWNLOAD SCRIPT ZIP#
I meant to include it in the demo download for my PASS session, also posted today, but forgot to put it into the ZIP file. The current bigAdventure script is attached to this post. So far I’ve been able to do most of what I need with only the two tables, but I hope to add more to the mix soon (for example, I’ve been working on a bigger version of Sales.CurrencyRate to help illustrate some SQLCLR techniques).
![adventureworks2012 download script adventureworks2012 download script](https://sqldestination.files.wordpress.com/2012/12/clip_image002_thumb.jpg)
Which is exactly what I need to emulate the large data warehouse queries we see in the real world. I've been fighting for a long time trying to get one of the myriad of adventureworks databases I've downloaded installed and running in SqlServer 2012 Express. The bigAdventure tables are several times larger than their AdventureWorks brethren, and allow me to easily create queries that overwhelm the 8 cores on my laptop. These tables are called dbo.bigProduct and dbo.bigTransactionHistory, and I refer to them collectively as bigAdventure. Instead I’ve started using a couple of tables modeled after Production.Product and Production.TransactionHistory. Fact is, they’re just a bit too small to show performance artifacts of parallelism, spilling to tempdb, and the like - the topics that I’m currently enamored with. However, In recent months I’ve been moving away from the core tables in the database. Since it was released I’ve used it almost exclusively for demos in talks I’ve written. I love the portability of AdventureWorks and the fact that anyone can download it. ,DATEPART(qq, dl.If the title of this post doesn’t have you scratching your head, you may have been paying very rapt attention last time you saw me speak. bak file anywhere or scripts that would create the. ,DATEPART(wk, dl.FullDate) as WeekOfYearNumber Hi, Our tests require AdventureWorks database from SQL Server 2005, but I cant seem to find either the.
![adventureworks2012 download script adventureworks2012 download script](http://4.bp.blogspot.com/-FIW10SZHcJ4/VmfuS_b6sJI/AAAAAAAAAI4/tLJujMVGMt8/s1600/sql%2Bserver%2B2016.jpg)
,DATEPART(dy,dl.FullDate) as DayOfYearNumber ,DATEPART(d,dl.FullDate) as DayOfMonthNumber ,DATENAME(weekday,dl.FullDate) as DayOfWeekName ,DATEPART(dw,dl.FullDate) as DayOfWeekNumber SELECT CONVERT(INT,CONVERT(VARCHAR,dl.FullDate,112)) as DateKey SET DATEFIRST 7 - Set the first day of the week to Monday WHERE dt_cte.FullDate < INTO FullDate FROM dt_cte SELECT DATEADD(DAY,1,FullDate) AS FullDate
![adventureworks2012 download script adventureworks2012 download script](https://www.mssqltips.com/tipimages2/6882_install-configure-sql-server-adventureworks-database.018.png)
Include more dates in Date dimension, the existing dates are not being replacedĭECLARE DATE = '' -change start date if DATE = -change end date if required ĭeclare int = - Delete leap year records (February 29)ĭelete from FactCurrencyRate where month() = 2 and day() = 29ĭelete from FactProductInventory where month() = 2 and day() = 29Īlter table FactCurrencyRate drop constraint FK_FactCurrencyRate_DimDateĪlter table FactFinance drop constraint FK_FactFinance_DimDateĪlter table FactInternetSales drop constraint FK_FactInternetSales_DimDateĪlter table FactInternetSales drop constraint FK_FactInternetSales_DimDate1Īlter table FactInternetSales drop constraint FK_FactInternetSales_DimDate2Īlter table FactProductInventory drop constraint FK_FactProductInventory_DimDateĪlter table FactResellerSales drop constraint FK_FactResellerSales_DimDateĪlter table FactSurveyResponse drop constraint FK_FactSurveyResponse_DateKey For example: if the current year is 2021, the data after running the script will be from 2017 to 2021.ĭeclare date = DATEADD (dd, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) +1, 0))
#ADVENTUREWORKS2012 DOWNLOAD SCRIPT UPDATE#
AdventureWorksDW original database contains data from 2010 to 2014, ths script will update the data to be (current year - 4 yars) to current year It uses the current year as the last year for the data in the Adventure Works database. The script updates the date colums for the AdventureWorksDW database with recent dates and it inserts new dates in the date dimension. You don’t want to have to explain why the data is old! However, the data is from 2014, making it difficult to showcase demos for stakeholders. The Microsoft AdventureWorks databases are excellent training resources. Why would you want to update your AdventureWorks Database?