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.
- 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.