Now the SQL Server and the management studio are ready for using. For training purpose, we need a database with some sample data. The sample database will be helpful for learning SQL Server. For this purpose, Microsoft has introduced the Wide World Importers Sample Database along with SQL Server 2016. Microsoft has published WideWorldImporters sample database as open source through GitHub.
What is Wide World Importers Sample Database?
The WideWorldImporters database is a sample SQL Server database released by Microsoft for beginners to learn SQL Server with the sample data and database objects like the tables, indexes, triggers, stored procedures, functions, etc. It is meant for standard online transaction processing scenarios. This sample database and the data is developed for a fictional company called Wide World Importers. This sample database will be helpful for those who are new and learning relational database management systems (RDBMS) using SQL Server.
Every new version of SQL Server has a corresponding version of sample database released within few days to illustrate the new features of the SQL Server version. Till SQL Server 2014, Microsoft released sample databases in the name of a fictional cycle manufacturing company called AdventureWorks. After the release of SQL Server 2016, A new sample database has been introduced. It’s called WideWorldImporters.
Choosing The SQL Server Sample Database
As of today WideWorldImporters database is in version 1.0. This version is compatible with SQL Server 2016 or higher. The database comes in 2 major types:
- WideWorldImporters: Sample database for online transaction processing (OLTP) which is commonly used SQL Server database engine.
- WideWorldImportersDW: Sample database for online analytical processing. This is the data warehouse sample database useful for playing with business intelligence features of the SQL Server. The data warehouse sample database depends on the OLTP sample database. So this database needs to be installed along with the OLTP sample.
These two basic types are further segregated and released as multiple artifacts for various editions of SQL Server. Here are the artifacts released as sample database. You have to choose the database based on the edition of SQL Server you have installed and the type of the service online transaction processing (OLTP) or online analytical processing (OLAP).
Sample WideWorldImporters Database Artifacts
- WideWorldImporters-Full.bak: Full sample database for OLTP and HTAP in backup format. This sample database is for the SQL Server enterprise edition, developer edition and the evaluation edition.
- WideWorldImportersDW-Full.bak: Full data warehouse sample database for OLAP in backup format. This data warehouse sample is also for SQL Server enterprise edition, developer edition and the evaluation edition.
- WideWorldImporters-Standard.bak: Sample OLTP database for standard edition of SQL Server in backup format.
- WideWorldImportersDW-Standard.bak: Sample OLAP data warehouse database for SQL Server standard edition in backup format.
- WideWorldImporters-Standard.bacpac: Sample standard OLTP database for Azure SQL Database Engine in bacpac format.
- WideWorldImportersDW-Standard.bacpac: Sample standard OLAP data warehouse database for Azure SQL Database Engine in bacpac format.
- Daily.ETL.ispac: SQL Server Integration Services (SSIS) package for Extraction, Transaction and Load (ETL) from both the OLTP and OLAP database samples. This ETL package is for enterprise, developer, evolution and standard editions of SQL Server.
- Sample-scripts.zip: Collection of sample scripts for experimenting the features of SQL Server using the data and objects in the WideWorldImporters database.
- Workload-drivers.zip: Sample windows application executables to be used along with the WideWorldImporters database.
Downloading WideWorldImporters Database
- You can download the WideWorldImporters Sample database from GitHub (https://github.com/Microsoft/sql-server-samples/releases/).
- From the list of database artifacts choose the file which you want and double-click to download it. I’ve installed SQL Server 2016 Developer Edition. So, I chose The full sample OLTP database (WideWorldImporters-Full.bak). This is in backup file format. It will be around 120 MB.
- After downloading, place the backup file (WideWorldImporters-Full.bak) under the default SQL Server 2016 backup folder. The default backup folder will be something like C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\.
- Now, follow one of the below methods to restore this backup file to the SQL Server.
Installing / Restoring WideWorldImporters Database
A. Restore Sample Database Using SQL Scripts
- Login to the SSMS (SQL Server Management Studio).
- Open a new SQL Query Editor window
- Copy the below code and paste it into the query editor window.
USE master RESTORE DATABASE WideWorldImporters FROM disk = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\WideWorldImporters-Full.bak' WITH MOVE 'WWI_Primary' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImporters.mdf', MOVE 'WWI_UserData' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImporters_UserData.ndf', MOVE 'WWI_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImporters.ldf', MOVE 'WWI_InMemory_Data_1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\WideWorldImporters_InMemory_Data_1', REPLACE
- Click the Execute icon in the toolbar. You will see the status message as in the below picture once the database is successfully restored.
- You can see the restored sample database (WideWorldImporters) in the Object Explorer under Databases folder. You may need to refresh the database list in the object explorer, if it is already open.
B. Restore Sample Database Using SSMS GUI
- Login to the SQL Server Management Studio (SSMS).
- In the Object Explorer, right-click the Database folder and select Restore Database... This will launch the restore database screen.
- In the Restore Database screen, choose Device and click the ellipsis button to launch the backup device selection screen.
- In the device selection window, press Add button to launch the file dialog. The file dialog will open the default SQL Server backup location. As we have already placed the backup file in that location, just select the backup file in the file dialog and press OK.
- Again press OK in the device selection window.
- Now, the restore database window is filled with the details of the database to be restored from the backup.
- Press the OK button in restore database window. The sample database backup is restored as a new database WideWorldImporters.
- You can see the restored sample database in the Object Explorer under Databases folder.
Generating More And Recent Data In WideWorldImporters Database
If you need more data in the Wide World Importers sample database, then you can use the inbuilt stored procedure DataLoadSimulation.PopulateDataToCurrentDate to generate more data. You can find more details about generating data in WideWorldImporters database here.
In my future article on SQL Server Basics series, I’ll be using this sample database for training.
- About WideWorldImporters sample database at https://docs.microsoft.com/en-us/sql/sample/world-wide-importers/wide-world-importers-documentation
- WideWorldImporters in GitHub at https://github.com/Microsoft/sql-server-samples/releases/