{"id":18262,"date":"2017-10-12T07:49:04","date_gmt":"2017-10-12T14:49:04","guid":{"rendered":"http:\/\/joedantoni.wordpress.com\/?p=1885"},"modified":"2017-10-12T07:49:04","modified_gmt":"2017-10-12T14:49:04","slug":"sql-server-2017-temporal-enhancements","status":"publish","type":"post","link":"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/","title":{"rendered":"SQL Server 2017 Temporal Enhancements"},"content":{"rendered":"

One of the most popular features in my talks about SQL Server 2016 has been the temporal tables feature. If you aren\u2019t familiar with this feature you can read more about it on Books Online here<\/a>. In a nutshell, you get a second table that tracks the lineage of your data. This is fantastic for all sorts of scenarios up to and including auditing, data recovery, fraud detection, or even slowly changing dimensions.<\/p>\n

\"th\"<\/a><\/p>\n

This is implemented in SQL Server via a history table\u2014a second copy of your data that maintains timestamps of when the data is valid. As you can imagine this table can grow quite large\u2014Microsoft does us a couple of favors: the history table is page compressed by default (you can use columnstore) and you could put the history table on a different filegroup. The only major issue was to truncate or delete data from history table for pruning purposes, you had to turn of system versioning, or the glue that makes that this feature work.<\/p>\n

Starting with SQL Server 2017 (and Azure SQL Database) you can define a retention period and have SQL Server prune records for you. This is awesome and easy\u2014see how to implement here<\/a>.<\/p>\n

\"\"<\/a> \"\"<\/p>\n","protected":false},"excerpt":{"rendered":"

One of the most popular features in my talks about SQL Server 2016 has been the temporal tables feature. If you aren’t familiar with this feature you can read more about it on Books Online here. In a nutshell, you get a second table that tracks the lineage of your data. This is fantastic for […]\"\"<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[551,63,271,46,147],"tags":[],"class_list":["post-18262","post","type-post","status-publish","format-standard","hentry","category-blog","category-sql","category-sql-server-2016","category-syndication","category-t-sql"],"yoast_head":"\nSQL Server 2017 Temporal Enhancements – Denny Cherry & Associates Consulting<\/title>\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.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2017 Temporal Enhancements – Denny Cherry & Associates Consulting\" \/>\n<meta property=\"og:description\" content=\"One of the most popular features in my talks about SQL Server 2016 has been the temporal tables feature. If you aren’t familiar with this feature you can read more about it on Books Online here. In a nutshell, you get a second table that tracks the lineage of your data. This is fantastic for […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/\" \/>\n<meta property=\"og:site_name\" content=\"Denny Cherry & Associates Consulting\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DennyCherryAssociatesConsulting\/\" \/>\n<meta property=\"article:published_time\" content=\"2017-10-12T14:49:04+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/th_thumb.jpg?w=244&h=226\" \/>\n<meta name=\"author\" content=\"Joey D'Antoni\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dcacco\" \/>\n<meta name=\"twitter:site\" content=\"@dcacco\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joey D'Antoni\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/\",\"url\":\"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/\",\"name\":\"SQL Server 2017 Temporal Enhancements – Denny Cherry & Associates Consulting\",\"isPartOf\":{\"@id\":\"https:\/\/www.dcac.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/th_thumb.jpg?w=244&h=226\",\"datePublished\":\"2017-10-12T14:49:04+00:00\",\"dateModified\":\"2017-10-12T14:49:04+00:00\",\"author\":{\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/ac58f2207d3f180aa3e4ee0ce6af0885\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/#primaryimage\",\"url\":\"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/th_thumb.jpg?w=244&h=226\",\"contentUrl\":\"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/th_thumb.jpg?w=244&h=226\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dcac.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2017 Temporal Enhancements\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dcac.com\/#website\",\"url\":\"https:\/\/www.dcac.com\/\",\"name\":\"Denny Cherry & Associates Consulting\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dcac.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/ac58f2207d3f180aa3e4ee0ce6af0885\",\"name\":\"Joey D'Antoni\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/3fede03569772a55a091b587d76f78d4?s=96&r=x\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/3fede03569772a55a091b587d76f78d4?s=96&r=x\",\"caption\":\"Joey D'Antoni\"},\"url\":\"https:\/\/www.dcac.com\/author\/joeydcac-co\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server 2017 Temporal Enhancements – Denny Cherry & Associates Consulting","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.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2017 Temporal Enhancements – Denny Cherry & Associates Consulting","og_description":"One of the most popular features in my talks about SQL Server 2016 has been the temporal tables feature. If you aren’t familiar with this feature you can read more about it on Books Online here. In a nutshell, you get a second table that tracks the lineage of your data. This is fantastic for […]","og_url":"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/","og_site_name":"Denny Cherry & Associates Consulting","article_publisher":"https:\/\/www.facebook.com\/DennyCherryAssociatesConsulting\/","article_published_time":"2017-10-12T14:49:04+00:00","og_image":[{"url":"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/th_thumb.jpg?w=244&h=226","type":"","width":"","height":""}],"author":"Joey D'Antoni","twitter_card":"summary_large_image","twitter_creator":"@dcacco","twitter_site":"@dcacco","twitter_misc":{"Written by":"Joey D'Antoni","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/","url":"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/","name":"SQL Server 2017 Temporal Enhancements – Denny Cherry & Associates Consulting","isPartOf":{"@id":"https:\/\/www.dcac.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/#primaryimage"},"image":{"@id":"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/#primaryimage"},"thumbnailUrl":"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/th_thumb.jpg?w=244&h=226","datePublished":"2017-10-12T14:49:04+00:00","dateModified":"2017-10-12T14:49:04+00:00","author":{"@id":"https:\/\/www.dcac.com\/#\/schema\/person\/ac58f2207d3f180aa3e4ee0ce6af0885"},"breadcrumb":{"@id":"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/#primaryimage","url":"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/th_thumb.jpg?w=244&h=226","contentUrl":"https:\/\/joedantoni.files.wordpress.com\/2017\/08\/th_thumb.jpg?w=244&h=226"},{"@type":"BreadcrumbList","@id":"https:\/\/www.dcac.com\/2017\/10\/12\/sql-server-2017-temporal-enhancements\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dcac.com\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2017 Temporal Enhancements"}]},{"@type":"WebSite","@id":"https:\/\/www.dcac.com\/#website","url":"https:\/\/www.dcac.com\/","name":"Denny Cherry & Associates Consulting","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dcac.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dcac.com\/#\/schema\/person\/ac58f2207d3f180aa3e4ee0ce6af0885","name":"Joey D'Antoni","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dcac.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/3fede03569772a55a091b587d76f78d4?s=96&r=x","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3fede03569772a55a091b587d76f78d4?s=96&r=x","caption":"Joey D'Antoni"},"url":"https:\/\/www.dcac.com\/author\/joeydcac-co\/"}]}},"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/18262","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/comments?post=18262"}],"version-history":[{"count":2,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/18262\/revisions"}],"predecessor-version":[{"id":18272,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/18262\/revisions\/18272"}],"wp:attachment":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/media?parent=18262"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/categories?post=18262"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/tags?post=18262"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}