Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 83 of 212

71 C H A P T E R 5 | Broader data access of 2 in the temporal_type column and locate its corresponding staging table in the sys.internal_tables table. Aggregate the values in the allocated_bytes and used_bytes columns of sys.dm_db_xtp_memory_consumers to view memory consumption for the specified memory-optimized table. Example 5-6: Viewing memory usage for temporal and internal staging tables WITH InMemoryTemporalTables AS ( SELECT SCHEMA_NAME ( T1.schema_id ) AS TemporalTableSchema , T1.object_id AS TemporalTableObjectId , IT.object_id AS InternalTableObjectId , OBJECT_NAME ( IT.parent_object_id ) AS TemporalTableName , IT.Name AS InternalHistoryStagingName FROM sys.internal_tables IT JOIN sys.tables T1 ON IT.parent_object_id = T1.object_id WHERE T1.is_memory_optimized = 1 AND T1.temporal_type = 2 ) , DetailedConsumption AS ( SELECT TemporalTableSchema , T.TemporalTableName , T.InternalHistoryStagingName , CASE WHEN C.object_id = T.TemporalTableObjectId THEN 'Temporal Table Consumption' ELSE 'Internal Table Consumption' END ConsumedBy , C.* FROM sys.dm_db_xtp_memory_consumers C JOIN InMemoryTemporalTables T ON C.object_id = T.TemporalTableObjectId OR C.object_id = T.InternalTableObjectId ) SELECT TemporalTableSchema , TemporalTableName , sum ( allocated_bytes ) AS allocated_bytes , sum ( used_bytes ) AS used_bytes FROM DetailedConsumption WHERE TemporalTableSchema = 'Production' AND TemporalTableName = 'ProductInventory_Temporal_InMemory' GROUP BY TemporalTableSchema, TemporalTableName; Querying temporal tables A standard query against a temporal table returns rows from the current table only. When you need to retrieve historical data, you can add the FOR SYSTEM_TIME clause to your SELECT statement with one of the following subclauses to define the boundaries of the data to return, as shown in Example 5-7: AS OF Returns the version of a row that was current at the specified date and time from either the current table or the history table. A row is considered current if the date/time parameter value in the subclause is equal to or later than the row's start period and less than its end period. FROM TO Returns any version of a row that was active in the specified range of dates from either the current table or the history table. A row is considered active if its end period is later than the start date/time parameter value and its start period is earlier than the end date/time parameter value.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016