Mastering SQL Server 2017

Build smart and efficient database applications for your organization with SQL Server 2017

Miloš Radivojević, Dejan Sarka, William Durkin, Christian Cote, Matija Lah

• Publisher: Packt Publishing
• Year: 2019
• ISBN:
  9781838987527
• Edition: 1
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

Leverage the power of SQL Server 2017 Integration Services to build data integration solutions with ease

Key Features

• Work with temporal tables to access information stored in a table at any time
• Get familiar with the latest features in SQL Server 2017 Integration Services
• Program and extend your packages to enhance their functionality

Book Description

Microsoft SQL Server 2017 uses the power of R and Python for machine learning and containerization-based deployment on Windows and Linux. By learning how to use the features of SQL Server 2017 effectively, you can build scalable apps and easily perform data integration and transformation.

You'll start by brushing up on the features of SQL Server 2017. This Learning Path will then demonstrate how you can use Query Store, columnstore indexes, and In-Memory OLTP in your apps. You'll also learn to integrate Python code in SQL Server and graph database implementations for development and testing. Next, you'll get up to speed with designing and building SQL Server Integration Services (SSIS) data warehouse packages using SQL server data tools. Toward the concluding chapters, you'll discover how to develop SSIS packages designed to maintain a data warehouse using the data flow and other control flow tasks.

By the end of this Learning Path, you'll be equipped with the skills you need to design efficient, high-performance database applications with confidence.

This Learning Path includes content from the following Packt books:

• SQL Server 2017 Developer's Guide by Miloš Radivojevi?, Dejan Sarka, et. al
• SQL Server 2017 Integration Services Cookbook by Christian Cote, Dejan Sarka, et. al

What you will learn

• Use columnstore indexes to make storage and performance improvements
• Extend database design solutions using temporal tables
• Exchange JSON data between applications and SQL Server
• Migrate historical data to Microsoft Azure by using Stretch Database
• Design the architecture of a modern Extract, Transform, and Load (ETL) solution
• Implement ETL solutions using Integration Services for both on-premise and Azure data

Who this book is for

This Learning Path is for database developers and solution architects looking to develop ETL solutions with SSIS, and explore the new features in SSIS 2017. Advanced analysis practitioners, business intelligence developers, and database consultants dealing with performance tuning will also find this book useful. Basic understanding of database concepts and T-SQL is required to get the best out of this Learning Path.

Temporal Tables

Databases that serve business applications often need to support temporal data. For example, suppose a contract with a supplier is valid for a limited time only. It could be valid from a specific point in time onward, or it could be valid for a specific time interval—from a starting time point to an ending time point. In addition, often you'll need to audit all changes in one or more tables. You might also need to be able to show the state at a specific point in time, or all changes made to a table in a specific period of time. From a data integrity perspective, you might need to implement many additional temporal-specific constraints.

This chapter introduces temporal problems, deals with manual solutions, and shows you out-of-the-box features in SQL Server 2016 and 2017, including the following:

• Defining temporal data
• Using temporal data in SQL Server before version 2016
• History of temporal data implementation
• System versioned tables in SQL Server 2016 and 2017
• What kind of temporal support is still missing in SQL Server 2017?

What is temporal data?

In a table with temporal support, the header represents a predicate with at least one time parameter that represents when the rest of the predicate is valid; the complete predicate is therefore a timestamped predicate. Rows represent timestamped propositions, and the row's valid time period is expressed with one of two attributes: since (for semi temporal data) or during (for fully temporal data); the latter attribute is usually represented with two values, from and to. The following table shows the original and two additional timestamped versions of an exemplary Suppliers table:

Original Suppliers table and two tables with temporal support

From the original table header, you can read a predicate saying that a supplier with identification supplierid, named companyname, with a contact contactname, and so on is currently our supplier, or is currently under contract. You pretend that this supplier is the supplier forever. The Suppliers_Since table header has this predicate modified with a time parameter; a supplier with the identification supplierid, named companyname, with a contact contactname, and so on has been under contract since some specific point in time. In the Suppliers_FromTo table, the header has this predicate modified with an even more specific time attribute; a supplier with the ID supplierid, named companyname, with a contact contactname, and so on is (or was, or will be, depending on the current time) under contract from some specific point in time to another point in time.

There is no need to implement semi-temporal tables. You can simply use the maximum possible date and time for the to time point. Therefore, the rest of the chapter focuses on fully temporal data only.

In this section, you will learn about:

• Types of temporal tables
• Temporal data algebra
• Temporal constrains
• Temporal data implementation in SQL Server before version 2016
• Optimization of temporal queries

Types of temporal tables

You might have noticed during the introduction part at the beginning of this chapter that there are two kinds of temporal issues. The first one is the validity time of the proposition—a time period in which the proposition that a timestamped row in a table represents was actually true. For example, a contract with a supplier was valid only from time point 1 to time point 2. This kind of validity time is meaningful to people and meaningful for the business. The validity time is also called application time or human time. We can have multiple valid periods for the same entity. For example, the aforementioned contract that was valid from time point 1 to time point 2 might also be valid from time point 7 to time point 9.

The second temporal issue is the transaction time. A row for the contract mentioned previously was inserted in time point 1 and was the only version of the truth known to the database until somebody changed it, or even to the end of time. When the row is updated in time point 2, the original row is known as being true to the database from time point 1 to time point 2. A new row for the same proposition is inserted with a time valid for the database from time point 2 to the end of time. The transaction time is also known as system time or database time.

The database management systems (DBMSs) can, and should, maintain the transaction times automatically. The system has to take care to insert a new row for every update and change the transaction validity period in the original row. The system also needs to allow for querying the current and the historical data, and show the state at any specific point in time. There are not many additional issues with the transaction time. The system has to take care that the start time of the database time period is lower than the end time, and that the two periods in two rows for the same entity don't overlap. The database system has to know a single truth at a single point in time. Finally, the database does not care about the future. The end of the database time of the current row is actually the end of time. Database time is about the present and past states only.

Implementing application time might be much more complex. Of course, you might have validity time periods that end or even begin in the future. DBMSs can't take care of future times automatically, and for example check whether they are correct. Therefore, you need to take care of all the constraints you need. The DBMS can only help you by implementing time-aware objects, such as declarative constraints. For example, a foreign key from the products to the suppliers table, which ensures that each product has a supplier, could be extended to check not only whether the supplier for the product exists, but also if the supplier is a valid supplier at the time point when the foreign key is checked.

So far, I've talked about time as though it consists of discrete time points; I used the term time point as if it represented a single, indivisible, infinitely small point in time. Of course, time is continuous. Nevertheless, in common language, we talk about time as though it consists of discrete points. We talk in days, hours, and other time units; the granularity we use depends on what we are talking about. The time points we are talking about are actually intervals of time; a day is an interval of 24 hours, an hour is an interval of 60 minutes, and so on.

So what is the granularity level of the time points for the system and application intervals? For the system times, the decision is simple: use the lowest granularity level that a system supports. In SQL Server, with the datetime2 data type, you can support 100-nanosecond granularity. For application time, the granularity depends on the business problem. For example, for a contract with a supplier, the day level could work well. For measuring the intervals when somebody is using services, such as mobile phone services, the granularity of seconds could be more appropriate. This looks very complex. However, you can make a generalized solution for the application times. You can translate time points to integers, and then use a lookup table that gives you the context—gives the meaning to the integer time points.

Of course, you can also implement both application and system versioned tables. Such tables are called bitemporal tables.

Allen's interval algebra

The theory for temporal data in a relational model started to evolve more than thirty years ago. I will define quite a few useful Boolean operators and a couple of op...

Table of contents

• Title Page
• Copyright
• Contributors
• About Packt
• Preface
• Introduction to SQL Server 2017
• SQL Server Tools
• JSON Support in SQL Server
• Stretch Database
• Temporal Tables
• Columnstore Indexes
• SSIS Setup
• What Is New in SSIS 2016
• Key Components of a Modern ETL Solution
• Dealing with Data Quality
• Unleash the Power of SSIS Script Task and Component
• On-Premises and Azure Big Data Integration
• Extending SSIS Custom Tasks and Transformations
• Scale Out with SSIS 2017
• Other Books You May Enjoy