{"id":6776,"date":"2016-01-08T14:48:27","date_gmt":"2016-01-08T13:48:27","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/"},"modified":"2016-01-08T14:48:27","modified_gmt":"2016-01-08T13:48:27","slug":"sql-server-2016-temporal-table-with-in-memory","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/","title":{"rendered":"SQL Server 2016: temporal table with In-Memory"},"content":{"rendered":"

In SQL Server 2016 CTP 3.0, temporal tables with In-Memory OLTP is supported. \ud83d\ude42
\nAt the JSS2015 (Journ\u00e9e SQL Server 2015)<\/a>, I have presented a little demo about temporal table with a table In-Memory.
\nThis article is a complement of my presentation and a good add to my series of Temporal Table.<\/p>\n

My first step is to create the temporal In-Memory table:<\/p>\n

CREATE TABLE [dbo].[Animals2]\n(\n\t[AnimalId] [int] IDENTITY(1,1) NOT NULL,\n\t[Name] [varchar](200) COLLATE French_CI_AS NOT NULL,\n\t[Genus Species] [varchar](200) COLLATE French_CI_AS NOT NULL,\n\t[Number] [int] NOT NULL,\n\t[StartDate] [datetime2](0) GENERATED ALWAYS AS ROW START NOT NULL,\n\t[EndDate] [datetime2](0) GENERATED ALWAYS AS ROW END NOT NULL,\n\tPERIOD FOR SYSTEM_TIME ([StartDate], [EndDate]),\n\n CONSTRAINT [Animals_primaryKey]  PRIMARY KEY NONCLUSTERED HASH \n(\n\t[AnimalId]\n)WITH ( BUCKET_COUNT = 131072)\n)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA, \nSYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[AnimalsHistory2] , DATA_CONSISTENCY_CHECK = ON ) )\n\nGO\n\n<\/pre>\n
\"TIMT01\"<\/a><\/p>\n
The first ascertainment in the sys.tables is that the History table is not In-Memory.
\nOnly the System-Versioned table is in In-Memory.
\nThe query to have the result:<\/p>\n
SELECT name,object_id,is_memory_optimized,durability,temporal_type,temporal_type_desc FROM sys.tables<\/pre>\n
I insert a value in the table and read the table:<\/p>\n
INSERT INTO [Zoo].[dbo].[Animals2]([Name],[Genus Species],[Number])\n     VALUES('Black swan','Cygnus atratus',12)\n\nSELECT * FROM [Zoo].[dbo].[Animals2]<\/pre>\n
\"TIMT02\"<\/a><\/p>\n
The value is present in the table and now I read the History table (who is empty of course)
\n\"TIMT03\"<\/a><\/p>\n
This error message comes from:<\/p>\n
 Msg 13582, Level 16, State 1, Line 7
\nTable ‘Zoo.dbo.AnimalsHistory2’ is history table for in-memory system-versioned temporal table and cannot be queried.<\/span>
\nI cannot read the history table and my first thing was to run some updates to read the history table with values.
\n\"TIMT04\"<\/a><\/p>\n
It is impossible to ask directly the History table and the only way is to use FOR SYSTEM_TIME ALL like this to have the History:<\/p>\n
SELECT *\n  FROM [Zoo].[dbo].[Animals2] FOR SYSTEM_TIME ALL WHERE Name='Black swan'<\/pre>\n
In the execution plan, the insert to the history table is not the same as a \u201cclassical\u201d temporal table
\n\"TIMT06\"<\/a><\/p>\n
The schema of a \u201cclassical\u201d temporal architecture is the current table and the history table.
\n\"temporal01\"<\/a><\/p>\n
But in an In-Memory mode, the temporal architecture introduces an Internal In-Memory History Table. This table is a \u201cbuffer table\u201d In-Memory to not disturb the speed of the Temporal In-Memory table
\n\"TIMT05\"<\/a>
\nThis Internal In-Memory History Table is called memory_optimized_history_table_\u201dtemporalTableId\u201d
\n\"TIMT07\"<\/a>
\nTo transfer values from Internal In-Memory History table to the History table, the stored procedure sp_xtp_flush_temporal_history can be used like this:<\/p>\n
exec sys.sp_xtp_flush_temporal_history [dbo], [Animals2]<\/pre>\n
The only way to read the history table is to disable the temporal mode.<\/p>\n
ALTER TABLE [Zoo].[dbo].[Animals2] SET (SYSTEM_VERSIONING = OFF) \nGO\nSELECT * FROM [Zoo].[dbo].[AnimalsHistory2]<\/pre>\n
\"TIMT08\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
In SQL Server 2016 CTP 3.0, temporal tables with In-Memory OLTP is supported. \ud83d\ude42 At the JSS2015 (Journ\u00e9e SQL Server 2015), I have presented a little demo about temporal table with a table In-Memory. This article is a complement of my presentation and a good add to my series of Temporal Table. My first step […]<\/p>\n","protected":false},"author":15,"featured_media":6785,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[48],"tags":[222,49,51,566],"type_dbi":[],"class_list":["post-6776","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology-survey","tag-in-memory","tag-microsoft","tag-sql-server","tag-sql-server-2016"],"acf":[],"yoast_head":"\nSQL Server 2016: temporal table with In-Memory<\/title>\n<meta name=\"description\" content=\"This article present Temporal Table in an In-Memory mode\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2016: temporal table with In-Memory\" \/>\n<meta property=\"og:description\" content=\"This article present Temporal Table in an In-Memory mode\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-08T13:48:27+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/TIMT08-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"559\" \/>\n\t<meta property=\"og:image:height\" content=\"347\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Haby\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Haby\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/\"},\"author\":{\"name\":\"St\u00e9phane Haby\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\"},\"headline\":\"SQL Server 2016: temporal table with In-Memory\",\"datePublished\":\"2016-01-08T13:48:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/\"},\"wordCount\":313,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/TIMT08-1.png\",\"keywords\":[\"In-memory\",\"Microsoft\",\"SQL Server\",\"SQL Server 2016\"],\"articleSection\":[\"Technology Survey\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/\",\"name\":\"SQL Server 2016: temporal table with In-Memory\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/TIMT08-1.png\",\"datePublished\":\"2016-01-08T13:48:27+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\"},\"description\":\"This article present Temporal Table in an In-Memory mode\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/TIMT08-1.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/TIMT08-1.png\",\"width\":559,\"height\":347},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2016: temporal table with In-Memory\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\",\"name\":\"St\u00e9phane Haby\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Haby\"},\"description\":\"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking \/ Financial Services, and many other industries.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server 2016: temporal table with In-Memory","description":"This article present Temporal Table in an In-Memory mode","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2016: temporal table with In-Memory","og_description":"This article present Temporal Table in an In-Memory mode","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/","og_site_name":"dbi Blog","article_published_time":"2016-01-08T13:48:27+00:00","og_image":[{"width":559,"height":347,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/TIMT08-1.png","type":"image\/png"}],"author":"St\u00e9phane Haby","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Haby","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/"},"author":{"name":"St\u00e9phane Haby","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"headline":"SQL Server 2016: temporal table with In-Memory","datePublished":"2016-01-08T13:48:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/"},"wordCount":313,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/TIMT08-1.png","keywords":["In-memory","Microsoft","SQL Server","SQL Server 2016"],"articleSection":["Technology Survey"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/","name":"SQL Server 2016: temporal table with In-Memory","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/TIMT08-1.png","datePublished":"2016-01-08T13:48:27+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"description":"This article present Temporal Table in an In-Memory mode","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/TIMT08-1.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/TIMT08-1.png","width":559,"height":347},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-temporal-table-with-in-memory\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2016: temporal table with In-Memory"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b","name":"St\u00e9phane Haby","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","caption":"St\u00e9phane Haby"},"description":"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking \/ Financial Services, and many other industries.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6776","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=6776"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6776\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/6785"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=6776"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=6776"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=6776"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=6776"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}