SQL Server Sample Databases (經典 / 示範資料庫)


  1. 說明
    1. Northwind
    2. AdeventureWorks
    3. Wide World Importers
    4. ConsotoUniversity
  2. 參考資料

筆記 SQL Server 熱門且實用的範例資料庫,可以用於程式開發、資料庫設計參考以及 SQL Server 功能驗證所使用,教學、自用兩相宜。

SQL Server Logo

說明

資料庫名稱 版本 下載
Northwind 英文 下載
Northwind 中文 下載
AdventureWorks Full 201420162019
AdventureWorks DW 201420162019
AdventureWorks LT 201420162019
Wide World Importers Full 下載
Wide World Importers DW 下載
Wide World Importers Standard 下載
Consoto University 下載

Northwind

資料庫的內容是一家販售各種食品的公司,資料表包含其員工、客戶、產品以及與客戶的交易紀錄。原先是微軟規劃用於介紹 Microsoft Access 功能所使用的範例資料庫,後續陸續應用至 SQL Server 上。

有使用到主鍵、外鍵、索引、非叢集索引、檢視表、預存程序等資料庫物件,可以供初入資料庫開發的學習使用。

因為過於知名,還有愛好者將其資料內容翻譯為中文版的北風資料庫。但中文版的北風資料庫僅有基本的資料表,缺乏檢視、預存程序等資料庫物件。

Northwind | GitHub

Hi, Do you have a minute to talk about Northwind Database?

Sure, What do you want to know?

Well, I’m curious about how it works and what kind of data it contains.

OK, let me give you a brief overview. Northwind Database is a sample database that was originally created by Microsoft.

Really? What was the purpose of creating it?

It was used as a basis for their tutorials in a variety of database products for decades. The database contains sales data for a fictitious company called Northwind Traders.

Wow, that sounds interesting. What kind of products does Northwind Traders sell?

They sell specialty foods from around the world. You can find products like cheese, tea, seafood, spices and more in their database.

That makes me hungry. How do they import and export these products?

They have suppliers and customers in different countries. The database has tables for suppliers, customers, orders, products and more.

AdeventureWorks

資料庫內容為一家腳踏車及零件販售公司,藉由結構描述將資料表分為五大類,分別是:人力資源、人員、產品、採購以及銷售。相較於 Northwind 更為豐富,使用到更多的資料庫物件。

版本依照,並依照資料庫內容分為 Lightweight (AdventureWorksLT)、Data Warehouse (AdventureWorksDW) 以及 OLTP (AdventureWorks) 等三種版本,其中 LT 版本是輕量級的示範性資料庫,適合用於測試資料查詢、程式介接測試;DW 版本則適合用於 OLAP 的商業智慧分析應用的測試資料庫。

AdventureWorks sample databases | GitHub

Hi, Do you have some time to talk about Adventure Works Database?

Hi, Sure, I do. What do you want to talk about?

Well, I’m interested in learning more about this database and how to use it.

OK, let me give you some background information. Adventure Works Database is a sample database that was created by Microsoft.

Really? What was the reason for creating it?

It was designed to provide an example of an online transaction processing (OLTP) database. It supports a fictitious multinational manufacturing company called Adventure Works Cycles.

Wow, that sounds cool. What kind of products does Adventure Works Cycles make?

They make bicycles and bicycle components You can find products like frames, wheels, brakes and more in their database.

That sounds fun. How do they sell and deliver these products?

They have customers and vendors in different countries. The database has tables for customers, vendors, sales orders, purchase orders and more.

I see. How can I access this database?

Well, if you have SQL Server installed on your computer, you can download it from GitHub by following the instructions there. Or you can create it on Azure SQL Database by using the sample option.

Thanks for the tips. Do you use this database often?

Yes, I do. It’s very helpful for practicing SQL queries and learning about OLTP databases.

Wide World Importers

目前微軟示範資料庫中最為完整架構的代表,全球規模的進口公司。資料庫使用到各種 SQL Server 的功能,包含 In-Memory Table、Dynamic Data Masking、Always Encrypted、Row Level Security 等示範。

wide-world-importers/sample-scripts | GitHub

完整版本的資料有使用到 FileStream 因此無法在 Express Localdb 上還原,但另有提供 Standard 版本的 bak 供於 Express Localdb 上還原使用,此外有提供 10 GB 等級的 OLAP 版本資料庫。配合 SQL Server 周邊工具,提供了 SSIS Package 專案供使用。

Wide World Importers | GitHub

Hi, Do you know what Wide World Importers is?

Hi, Yes, I do. It’s a sample database for SQL Server.

Really? What kind of data does it contain?

It contains data about a fictitious company that imports and sells novelty goods. The company has customers and suppliers around the world.

ConsotoUniversity

微軟用於介紹 EntityFramework 技術所使用的資料庫,內容為一所大學所開設的課程以及學生、教師與修課資訊。

資料庫的架構簡單,主要不是作為 SQL Server Query 的應用,而是介紹系統開發如何使用 EntityFramework 以 Code-First 的方式從 Code 產生 DAtabase。

School Sample Database

Hi, Do you know what Consoto University is?

Hi, Yes, I do. It’s a fictitious organization that Microsoft uses as an example company name and domain name.

Really? What is the purpose of using it?

It’s used for demonstrating various features and technologies of Microsoft products. For example, it’s used for showing how to use ASP.NET Core and related technologies.

That sounds interesting. How can I learn more about it?

Well, if you are interested in ASP.NET Core development, you can check out this GitHub repository that contains a demo application for Consoto University. It shows how to use different concepts and techniques in a larger context.

參考資料

Collection: SQL Server Sample Databases

The new SQL Server 2016 sample database