{"id":9056,"date":"2016-10-08T12:03:46","date_gmt":"2016-10-08T10:03:46","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/"},"modified":"2016-10-08T12:03:46","modified_gmt":"2016-10-08T10:03:46","slug":"sql-server-2016-truncate-partitions-with-sliding-windows-scenarios","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/","title":{"rendered":"SQL Server 2016: TRUNCATE PARTITIONS with sliding Windows scenarios"},"content":{"rendered":"<p>Some time\u00a0ago, I had to deal with a new partitioning scenario that included sliding windows stuff for mainly archiving purpose. Regarding the customer context, I used some management scripts that include this time dropping oldest partition. We didn\u2019t care about data oldest than 2 years. Usually in this case, I use a method that consists in dropping data by switching first the oldest partition to a staging table and then truncate it. Finally we may safely merge oldest partitions and avoid any data movement. At a first glance, it seems to be a complex process for dropping data but until SQL Server 2014 there is no way to do better in order to minimize operation logging.<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-0-partitioning.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11541\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-0-partitioning.jpg\" alt=\"blog 105 - 0 - partitioning\" width=\"241\" height=\"241\" \/><\/a><\/p>\n<p>This week, I had the opportunity to work with SQL Server 2016 to learn about new partition improvements. By the way, the only thing I could find out from my different internet researches concerned the new command TRUNCATE TABLE WITH PARTITIONS.<\/p>\n<p>My first feeling was it is not a very exciting feature in contrast to previous versions that provided a mixture of both performance and maintenance improvements in this field. But after investigating further, I was able to point out some advantages to use this command. Let\u2019s go back to my sliding windows scenario. In order to drop data from my oldest partition I have to:<\/p>\n<ul>\n<li>Switch the oldest partition to a staging table<\/li>\n<li>Drop data from this staging table with TRUNCATE command in order to minimize transaction logging<\/li>\n<li>Execute MERGE command in order to slide all partitions to the left side<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>What about introducing the new TRUNCATE TABLE command in this scenario?<\/strong><\/p>\n<p>In fact, it will simplify the above process by replacing step 1 and step 2 by the TRUNCATE command at the partition level. The new scenario becomes:<\/p>\n<ul>\n<li>TRUNCATE TABLE at the corresponding partition<\/li>\n<li>Execute MERGE command in order to slide all partitions to the left<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #000000;font-family: Calibri\">The only instruction I need to use is as follows:<\/span><\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">TRUNCATE TABLE [dbo].[FactOnlineSales]\nWITH ( PARTITIONS (2) );<\/pre>\n<p>&nbsp;<\/p>\n<p><strong>What about locking?<\/strong><\/p>\n<p>As expected, SQL Server will use a lock granularity hierarchy with a mixture of Sch-S, Sch-M and X locks regarding the corresponding locked resource. You may see two allocation units in my case because I\u2019m using a partitioned clustered columnstore index in this demo. As a reminder, compressed columnstore segments are stored in LOB.<\/p>\n<p>&nbsp;<\/p>\n<table>\n<tbody>\n<tr>\n<td width=\"196\">Object<\/td>\n<td width=\"196\">Resource type<\/td>\n<td width=\"196\">Resource subtype<\/td>\n<td width=\"196\">Resource description<\/td>\n<td width=\"492\">Associated entity<\/td>\n<td width=\"177\">Lock request mode<\/td>\n<\/tr>\n<tr>\n<td width=\"196\">OBJECT<\/td>\n<td width=\"196\"><\/td>\n<td width=\"196\"><\/td>\n<td width=\"196\"><\/td>\n<td width=\"492\">FactOnlineSales<\/td>\n<td width=\"177\">Sch-M<\/td>\n<\/tr>\n<tr>\n<td width=\"196\">METADATA<\/td>\n<td width=\"196\">DATA_SPACE<\/td>\n<td width=\"196\"><\/td>\n<td width=\"196\">data_space_id = 3<\/td>\n<td width=\"492\">Columnstore2007 (filegroup that relies on partition nb 2)<\/td>\n<td width=\"177\">Sch-M<\/td>\n<\/tr>\n<tr>\n<td width=\"196\">HOBT<\/td>\n<td width=\"196\"><\/td>\n<td width=\"196\"><\/td>\n<td width=\"196\"><\/td>\n<td width=\"492\">Partition nb 2<\/td>\n<td width=\"177\">Sch-M<\/td>\n<\/tr>\n<tr>\n<td width=\"196\">ALLOCATION_UNIT<\/td>\n<td width=\"196\"><\/td>\n<td width=\"196\"><\/td>\n<td width=\"196\"><\/td>\n<td width=\"492\">Related to data_space_id = 3 with state = DROPPED (LOB_DATA)<\/td>\n<td width=\"177\">X<\/td>\n<\/tr>\n<tr>\n<td width=\"196\">ALLOCATION_UNIT<\/td>\n<td width=\"196\"><\/td>\n<td width=\"196\"><\/td>\n<td width=\"196\"><\/td>\n<td width=\"492\">Related to data_space_id = 3 \u00a0 \u00a0 (IN_ROW_DATA)<\/td>\n<td width=\"177\">X<\/td>\n<\/tr>\n<tr>\n<td width=\"196\">KEY<\/td>\n<td width=\"196\"><\/td>\n<td width=\"196\"><\/td>\n<td width=\"196\"><\/td>\n<td width=\"492\">Records in the partition 2<\/td>\n<td width=\"177\">X<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>What about logging?<\/strong><\/p>\n<p>Well, if I refer to the corresponding records into the transaction log file, TRUNCATE partition command seems to act as a normal TRUNCATE operation. Firstly, we may notice few records generated related to marking the concerned structures to drop and then the deferred drop mechanism comes into play by deallocating them.<\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-1-e1475927789130.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11531\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-1-e1475927789130.jpg\" alt=\"blog 105 - 1 - truncate partitions tlog 1\" width=\"800\" height=\"256\" \/><\/a><\/p>\n<p>&#8230;<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-2-e1475927856449.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11532\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-2-e1475927856449.jpg\" alt=\"blog 105 - 1 - truncate partitions tlog 2\" width=\"800\" height=\"103\" \/><\/a><\/p>\n<p>&#8230;<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-3-e1475927901693.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11533\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-3-e1475927901693.jpg\" alt=\"blog 105 - 1 - truncate partitions tlog 3\" width=\"800\" height=\"127\" \/><\/a><\/p>\n<p>&#8230;<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-4-e1475927955989.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-11534\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-4-e1475927955989.jpg\" alt=\"blog 105 - 1 - truncate partitions tlog 4\" width=\"800\" height=\"111\" \/><\/a><\/p>\n<p><span style=\"color: #000000;font-family: Calibri\">Happy partitioning!<\/span><\/p>\n<p>By David Barbarin<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Some time\u00a0ago, I had to deal with a new partitioning scenario that included sliding windows stuff for mainly archiving purpose. Regarding the customer context, I used some management scripts that include this time dropping oldest partition. We didn\u2019t care about data oldest than 2 years. Usually in this case, I use a method that consists&hellip;&nbsp;<\/p>\n","protected":false},"author":26,"featured_media":9062,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133],"tags":[366,566],"class_list":["post-9056","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","tag-partitioning","tag-sql-server-2016"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.4 (Yoast SEO v28.7) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL 2016: TRUNCATE PARTITIONS with sliding Windows scenarios<\/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.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2016: TRUNCATE PARTITIONS with sliding Windows scenarios\" \/>\n<meta property=\"og:description\" content=\"Some time\u00a0ago, I had to deal with a new partitioning scenario that included sliding windows stuff for mainly archiving purpose. Regarding the customer context, I used some management scripts that include this time dropping oldest partition. We didn\u2019t care about data oldest than 2 years. Usually in this case, I use a method that consists&hellip;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2016-10-08T10:03:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-1-e1475927789130-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"800\" \/>\n\t<meta property=\"og:image:height\" content=\"256\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 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-truncate-partitions-with-sliding-windows-scenarios\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\\\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/857c83b2f1d7d3e7c113ed40edf63294\"},\"headline\":\"SQL Server 2016: TRUNCATE PARTITIONS with sliding Windows scenarios\",\"datePublished\":\"2016-10-08T10:03:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\\\/\"},\"wordCount\":521,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/blog-105-1-truncate-partitions-tlog-1-e1475927789130-1.jpg\",\"keywords\":[\"Partitioning\",\"SQL Server 2016\"],\"articleSection\":[\"Database\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\\\/\",\"name\":\"SQL 2016: TRUNCATE PARTITIONS with sliding Windows scenarios\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/blog-105-1-truncate-partitions-tlog-1-e1475927789130-1.jpg\",\"datePublished\":\"2016-10-08T10:03:46+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/blog-105-1-truncate-partitions-tlog-1-e1475927789130-1.jpg\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/04\\\/blog-105-1-truncate-partitions-tlog-1-e1475927789130-1.jpg\",\"width\":800,\"height\":256},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2016: TRUNCATE PARTITIONS with sliding Windows scenarios\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"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\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/857c83b2f1d7d3e7c113ed40edf63294\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/microsoft-square.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/microsoft-square.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/microsoft-square.png\",\"caption\":\"Microsoft Team\"},\"description\":\"Posts published under the Microsoft Team account are collective contributions by the dbi services Microsoft consultants. The practice covers SQL Server across its lifecycle, from installation, migration, and security to high availability with Always On and performance tuning, together with Azure data services and the Microsoft Business Intelligence stack, including Power BI. The team holds Microsoft certifications up to Expert level and operates customer platforms under service level agreements.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/microsoft-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL 2016: TRUNCATE PARTITIONS with sliding Windows scenarios","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-truncate-partitions-with-sliding-windows-scenarios\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2016: TRUNCATE PARTITIONS with sliding Windows scenarios","og_description":"Some time\u00a0ago, I had to deal with a new partitioning scenario that included sliding windows stuff for mainly archiving purpose. Regarding the customer context, I used some management scripts that include this time dropping oldest partition. We didn\u2019t care about data oldest than 2 years. Usually in this case, I use a method that consists&hellip;&nbsp;","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/","og_site_name":"dbi services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2016-10-08T10:03:46+00:00","og_image":[{"width":800,"height":256,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-1-e1475927789130-1.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/857c83b2f1d7d3e7c113ed40edf63294"},"headline":"SQL Server 2016: TRUNCATE PARTITIONS with sliding Windows scenarios","datePublished":"2016-10-08T10:03:46+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/"},"wordCount":521,"commentCount":0,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-1-e1475927789130-1.jpg","keywords":["Partitioning","SQL Server 2016"],"articleSection":["Database"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/","name":"SQL 2016: TRUNCATE PARTITIONS with sliding Windows scenarios","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-1-e1475927789130-1.jpg","datePublished":"2016-10-08T10:03:46+00:00","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-1-e1475927789130-1.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/04\/blog-105-1-truncate-partitions-tlog-1-e1475927789130-1.jpg","width":800,"height":256},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-truncate-partitions-with-sliding-windows-scenarios\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2016: TRUNCATE PARTITIONS with sliding Windows scenarios"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","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":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/857c83b2f1d7d3e7c113ed40edf63294","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/microsoft-square.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/microsoft-square.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/microsoft-square.png","caption":"Microsoft Team"},"description":"Posts published under the Microsoft Team account are collective contributions by the dbi services Microsoft consultants. The practice covers SQL Server across its lifecycle, from installation, migration, and security to high availability with Always On and performance tuning, together with Azure data services and the Microsoft Business Intelligence stack, including Power BI. The team holds Microsoft certifications up to Expert level and operates customer platforms under service level agreements.","url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9056","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\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9056"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9056\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/9062"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9056"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9056"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9056"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}