For many years Microsoft has shipped example databases with its database products, SQL Server and MS Access. In this article I have listed five that are useful in training and testing. Also included are download links and some practical installation information.
Example Databases
Web Link » Northwind and Pubs
The earliest example database from Microsoft was Pubs, which according to this article came from SYBASE and has been around since the nineties. Northwind came later as an example database for Microsoft Access and was later ported to SQL 2000. Pubs is a book vendor and Northwind Traders is an import/export company.They are both old and rather simplistic compared to modern databases. That being said, they are still useful for learning database fundamentals partially due to that simplicity.
After clicking on the link above, use the Download button to download SQL2000SampleDb.msi. Double-click to install and it will install to a folder named SQL Server 2000 Sample Databases in the root of C:\. You can then move the folder anywhere. Open the newly created folder and unless you have some reason to keep them delete all the SQL Database files with file extension MDF and LDF. You can't use them anyway, unless your database server is SQL 2000.
To use either of these databases in later versions of SQL Server we need to execute the query script. You can do this in Visual Studio, but I prefer using SQL Server Management Studio.
1. Login to MS SQL Server Management Studio and from the main menu go to File > Open > File. Browse to the SQL Server 2000 Sample Databases folder that was created from the downloaded installation file, click on either instpubs.sql or instnwnd.sql, and click Open.
2. Take a look and the query to see what it is going to create. Pubs is over 2000 lines and Northwind is over 9000 lines but they will create the databases in just a few seconds. When you are ready, click the Execute button from the SQL Editor toolbar and the scripts will run.
Both Pubs and Northwind scripts with execute completely with one error each. And it is the same error, for a missing stored procedure that is no longer used. The error reads, "Could not find stored procedure 'sp_dboption'." Since we are not using replication, we can ignore this error.
3. Right-click on Databases in the Object Explorer and choose Refresh. The database you just created should appear. Click the + to expand the database nodes and right-click any table and choose Edit Top 200 rows to see the table's records. You can install both Northwind and Pubs for use in various projects.
This database should now be ready to work with in Visual Studio.
Web Link » AdventureWorks2014
AdventureWorks is for SQL Server. The database has evolved since its introduction in 2005. Each iteration has been updated for the latest versions of SQL Server. You need to install the version that corresponds to the version of SQL Server you are using. The latest version of SQL Server Express is 2014, so that is the link I have provided. You can also find previous versions at CodePlex.
Unless you specifically want to create AdventureWorks2014 from scripts, I would recommend downloading the uppermost download, which links to the file Adventure Works 2014 Full Database Backup.zip. You can use the restore function of SQL Server to install the database from this backup.
1. From the link above download the Adventure Works 2014 Full Database Backup.zip. After downloading, right-click on the ZIP file and choose Extract All. You can save it anywhere and move it later.
If you want to put it into the SQL Server backup folder, then save it to the following path for 32 bit Windows:
C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup.
2. From within the Server Management Studio Object Explorer, right-click on Databases and choose Restore.
3. Click the radial button for Device: and click the ellipsis to the right of the field. Make sure the Media Type is File and click Add. Browse to where you saved your backup file and highlight it, click OK and OK again.
4. The rest of the fields in the restore dialog will populate. Click OK to begin the recovery. After completion, follow step 3. from the Pubs installation to confirm the restore for AdventureWorks. If you see table data, then it should be ready to play with in Visual Studio.
Web Link » World Wide Importers and Contoso
World Wide Importers and Contoso Data Warehouse are the two most sophisticated example databases from Microsoft. They show off the capabilities of SQL Server 2016 and Azure. There is no Express version of SQL Server 2016 yet, so we will not be using these databases.
To learn how to retrieve data from the Northwind database and use ASP.net controls to view it on a web page, go to my next article. Also, see my Technology Category for more tutorials, walkthroughs and lessons using the example databases shown here.