Converting a non-temporal table into a temporal table is very easy. With two ALTER TABLE statements, you get the full temporal table functionality. However, this cannot be completely free; adding a temporal feature to a table brings a performance overhead in data modification operations. According to my measurements, a single update operation against a row in a temporal table is about 30% slower than it would be when the table is non-temporal. I performed a simple test where I updated a different number of rows in a table before it was converted to a temporal table and after that. Figure 7.8 displays the results of this test. For a small amount of rows (<1000) locking, calculating, and inserting data into the history table slows down the update about two times. Updating 50,000 rows in a temporal table takes about 8 times longer than the same operation against the same table when it is implemented as a non-temporal table.

Figure 7.8: Temporal table updates overhead

Finally, massive updates (>100K rows) are 10 times slower for temporal tables compared to those against their non-temporal counterparts.

System-versioned temporal tables are also supported for memory-optimized tables. You can assign or let SQL Server create a history table for your memory-optimized table. The history table must be a disk table, but this is exactly what you want: frequently used (hot) data remains in memory, while cold data can reside in disk tables. By taking this approach, you can use all the benefits provided by memory-optimized tables (high transactional throughput, lock-free concurrency) and save their historical data on disk-based tables and leave memory for active datasets only.

Figure 7.9 shows the architecture of system-versioned memory-optimized tables. It is taken from the Books Online page at https://msdn.microsoft.com/en-us/library/mt590207.aspx.

Figure 7.9: System-Versioned Temporal Tables with Memory-Optimized Tables Architecture

As you can see, system-versioned temporal tables are implemented with three tables:

Historical data is a union of data in the recent history and history tables. A history row is either in the staging memory table or in the disk table: it cannot be in both tables. The following code example creates a new memory-optimized temporal table:

USE WideWorldImporters; 
CREATE TABLE dbo.Product 
( 
   ProductId INT NOT NULL PRIMARY KEY NONCLUSTERED, 
   ProductName NVARCHAR(50) NOT NULL, 
   Price MONEY NOT NULL, 
   ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, 
   ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, 
   PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) 
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA, SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory)); 

After the execution of this query, you can see that one memory-optimized table is:

SELECT CONCAT(SCHEMA_NAME(schema_id),'.', name) AS table_name, is_memory_optimized, temporal_type_desc FROM sys.tables WHERE name IN ('Product', 
 'ProductHistory'); 

The result of the preceding query is as follows:

table_name        is_memory_optimized temporal_type_desc
----------------- ------------------- ------------------------------
dbo.Product         1                 SYSTEM_VERSIONED_TEMPORAL_TABLE
dbo.ProductHistory  0                 HISTORY_TABLE

As mentioned earlier, SQL Server creates a third table automatically: an internal memory-optimized table. Here is the code that you can use to find its name and properties:

SELECT CONCAT(SCHEMA_NAME(schema_id),'.', name) AS table_name, internal_type_desc FROM  sys.internal_tables WHERE name = CONCAT('memory_optimized_history_table_', OBJECT_ID('dbo.Product')); 

And here is its output:

Only durable, memory-optimized tables can be system-versioned temporal tables and history tables must be disk-based. Since all current rows are in memory, you can use natively compiled modules to access this data. Use the following code to create a native compiled stored procedure that handles the inserting and updating of products:

CREATE OR ALTER PROCEDURE dbo.SaveProduct   
(    
@ProductId INT, 
@ProductName NVARCHAR(50), 
@Price MONEY 
)    
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER    
AS     
   BEGIN ATOMIC WITH    
   (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')    
   UPDATE dbo.Product SET ProductName = @ProductName, Price = @Price    
   WHERE ProductId = @ProductId 
   IF @@ROWCOUNT = 0 
         INSERT INTO dbo.Product(ProductId,ProductName,Price) VALUES (@ProductId, @ProductName, @Price); 
END 
GO 

Now you can, for instance, add two rows and update one of them by using the above procedure:

EXEC dbo.SaveProduct 1, N'Home Jersey Benfica', 89.95; 
EXEC dbo.SaveProduct 2, N'Away Jersey Juventus', 89.95; 
EXEC dbo.SaveProduct 1, N'Home Jersey Benfica', 79.95; 

Under the hood, everything works perfectly: both the current and history tables are updated. Here are the resulting datasets:

Note that values in the column ValidFrom and ValidTo are shortened to fit in a single line.

The querying of historical data is effectively under the SNAPSHOT isolation level and always returns a union between the in-memory staging buffer and the disk based table without duplicates. Since temporal queries (queries that use the FOR SYSTEM_TIME clause) are touching memory-optimized and disk tables, they can be used only in the interop mode; it is not possible to use them in native compiled procedures.

Data from the internal memory-optimized staging table is regularly moved to the disk-based history table by the asynchronous data flush task. This data flush mechanism has the goal of keeping the internal memory buffers at less than 10% of the memory consumption of their parent objects.

When you add system-versioning to an existing non-temporal table, expect a performance impact on update and delete operations because history table is updated automatically. Every update and delete is recorded into the internal memory-optimized history table, so you may experience unexpected memory consumption if your workload uses those two operations massively.