Use of SQL Server features and capabilities
WideWorldImporters use of SQL Server features and capabilities in the OLTP database.
WideWorldImporters is designed to showcase many of the key features of SQL Server, including the latest features introduced in SQL Server 2016. The following is a list of SQL Server features and capabilities, and a description of how they are used in WideWorldImporters.
|SQL Server feature or capability||Use in WideWorldImporters|
|Temporal tables||There are many temporal tables, including all look-up style reference tables and main entities such as StockItems, Customers, and Suppliers. Using temporal tables allows to conveniently keep track of the history of these entities.|
|AJAX calls for JSON||The application frequently uses AJAX calls to query these tables: Persons, Customers, Suppliers, and StockItems. The calls return JSON payloads (i.e. the data that is returned is formatted as JSON data). See, for example, the stored procedure
|JSON property/value bags||A number of tables have columns that hold JSON data to extend the relational data in the table. For example,
|Row-level security (RLS)||Row Level Security (RLS) is used to limit access to the Customers table, based on role membership. Each sales territory has a role and a user. To see this in action, use the corresponding script in sample-script.zip, which is part of the release of the sample.|
|Real-time Operational Analytics||(Full version of the database) The core transactional tables
|PolyBase||To see this PolyBase in action, using an external table with a public data set hosted in Azure blog storage, use the corresponding script in sample-script.zip, which is part of the release of the sample.|
|In-Memory OLTP||(Full version of the database) The table types are all memory-optimized, such that table-valued parameters (TVPs) all benefit from memory-optimization.
The two monitoring tables,
The stored procedure
To see an example of In-Memory OLTP in action, see the vehicle-locations workload driver in workload-drivers.zip, which is part of the release of the sample.
|Clustered columnstore index||(Full version of the database) The table
|Dynamic Data Masking||In the database schema, Data Masking has been applied to the bank details held for Suppliers, in the table
|Always Encrypted||A demo for Always Encrypted is included in the downloadable samples.zip, which is part of the release of the sample.. The demo creates an encryption key, a table using encryption for sensitive data, and a small sample application that inserts data into the table.|
|Full-text indexes||Full-text indexes improve searches for People, Customers, and StockItems. The indexes are applied to queries only if you have full-text indexing installed on your SQL Server instance. A non-persistent computed column is used to create the data that is full-text indexed in the StockItems table.
To enable the use of full-text indexes in the sample execute the following statement in the database:
The procedure creates a default fulltext catalog if one doesn’t already exist, then replaces the search views with full-text versions of those views).
Note that using full-text indexes in SQL Server requires selecting the Full-Text option during installation. Azure SQL Database does not require and specific configuration to enable full-text indexes.
|Indexed persisted computed columns||Indexed persisted computed columns used in SupplierTransactions and CustomerTransactions.|
|Check constraints||A relatively complex check constraint is in
|Unique constraints||A many to many construction (and unique constraints) are set up for Warehouse.StockItemStockGroups`.|
|Table partitioning||(Full version of the database) The tables
|List processing||An example table type
|Query Store||Query Store is enabled on the database. After running a few queries, open the database in Management Studio, open the node Query Store, which is under the database, and open the report Top Resource Consuming Queries to see the query executions and the plans for the queries you just ran.|
|Audit||SQL Server Audit can be enabled for this sample database by running the following statement in the database:
In Azure SQL Database, auditing is enabled through the Azure portal.
Security operations involving logins, roles and permissions are logged on all systems where audit is enabled (including standard edition systems). Audit is directed to the application log because this is available on all systems and does not require additional permissions. A warning is given that for higher security, it should be redirected to the security log or to a file in a secure folder. A link is provided to describe the required additional configuration.
For evaluation/developer/enterprise edition systems, access to all financial transactional data is audited.