New SQL Server Sample Database: WideWorldImporters

The sample database for SQL Server known as AdventureWorks, which has been around since the SQL Server 2005 days, had been replaced with a new sample database called WideWorldImporters. It is intended to showcase some of the newer SQL Server 2016 features, capabilities, and performance enhancements.

Microsoft says:

  • WideWorldImporters is the main database for transaction processing (OLTP – OnLine Transaction Processing) and operational analytics (HTAP – Hybrid Transactional/Analytics Processing). Here are some examples of the use of SQL Server capabilities with this database:
    • Real-time operational analytics of sales data is enabled through the use of nonclustered columnstore indexes.
    • Archive tables can be stretched to Azure for long-term retention, reducing storage cost and improving manageability.
    • Query Store is used to keep track of query performance.
    • Temporal tables are used to conveniently keep track of the history of reference data, as well as some of the main entities.
    • JSON is used to enable AJAX calls to some of the key tables, and also to extend the relational schema to record such things as application settings and user preferences.
    • Advanced security features like Always Encrypted, Row-Level Security and Dynamic Data Masking are used to secure data.
    • In-Memory OLTP is used to optimize the performance of table-valued parameters (TVPs) and to optimize ingestion of sensor data.
    • Clustered columnstore indexes are used to reduce the storage footprint of large tables with insert-only workload.
    • Partitioning is used to improve the manageability of large tables.
    • For more detail, see WideWorldImporters use of SQL Server features and capabilities.
  • WideWorldImportersDW is the main database for data warehousing and analytics (OLAP – OnLine Analytics Processing). The data in this database is derived from the transactional database WideWorldImporters, but it uses a schema that is specifically optimized for analytics. Here are some examples of the use of SQL Server capabilities with this database:
    • Clustered columnstore indexes are used to reduce the storage footprint and improve query performance for the fact tables.
    • PolyBase is used to correlate data in the local database with a public data set in Azure Blog storage.
    • In-Memory OLTP is used to improve the performance of the ETL process.
    • Partitioning is used to improve manageability of the fact tables, which can grow very large in a data warehouse.
    • For more detail, see WideWorldImportersDW use of SQL Server features and capabilities.
  • A SQL Server Integration Services (SSIS) package, Daily ETL.ispac, is used to move data from the OLTP database WideWorldImporters to the OLAP database WideWorldImportersDW. The package is designed to use bulk T-SQL statements wherever possible to enhance performance. For details about the ETL workflow, see the WideWorldImportersDW ETL workflow.

Download Link

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s