{"id":281,"date":"2019-04-20T18:00:38","date_gmt":"2019-04-20T18:00:38","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=281"},"modified":"2019-04-14T18:25:23","modified_gmt":"2019-04-14T18:25:23","slug":"partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/","title":{"rendered":"Partitions in SQL Server: Partitioning a Table on a non Primary Key Column"},"content":{"rendered":"<p>In my series on partitioning, I&#8217;ve looked at creating a <a href=\"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/\">fact table partitioned by date<\/a>. But I recently came across a case where I&#8217;d got data which had a date which should be used for the partition, however there was also a natural key in the data which should be maintained as a primary key.<\/p>\n<h2>Components of a Partitioned Table<\/h2>\n<p>Just to recap (and for those who have landed here after a google search). The key components which make up a partitioned table in SQL Server are:<\/p>\n<ol>\n<li><strong>Partition Function<\/strong>: The definition of how data is to be split. It includes the data type, and the value ranges to use in each partition.<\/li>\n<li><strong>Partition Scheme<\/strong>: The definition of how a <em>partition function<\/em> is to be applied to data files. This allows DBAs to split data across logical storage locations if required, however in most modern environments with large SANs most SQL Server implementations and their DBAs will just use \u2018primary\u2019.<\/li>\n<li><strong>Table &amp; Index Definition<\/strong>: The last part is the definition of the table, including the <em>partition scheme<\/em> to use, and the index with which to apply it.<\/li>\n<\/ol>\n<h1>Creating The Partitioned Table<\/h1>\n<p>For the benefit of those who have landed here by google, and those of us who like to copy\/paste), here is the function and scheme sample first.<\/p>\n<h2>Partition Function<\/h2>\n<p>For this dataset, I\u2019m going to add a new partition for each month (functions can be for any time frame you chose):<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">--------------------------------------------------------------------------------\r\n-- Partition Function\r\n-- Using RIGHT (so the value given is the LOWER value)\r\n--------------------------------------------------------------------------------\r\nCREATE PARTITION FUNCTION [FactTable_PF]\r\n      (DATE) -- Datatype to use\r\nAS\r\nRANGE RIGHT  -- See previous post\r\n FOR VALUES  -- Values on which to 'split' the data\r\n(\r\n    N'2017-01-01T00:00:00', -- This will contain all data for 2017 Jan onwards (until the next partition). All data before then goes into a 'catch all' partition\r\n    N'2018-01-01T00:00:00', -- This partition will contain all data from Jan 2018 onwards (until the next partition).\r\n    N'2019-01-01T00:00:00'  -- This partition will contain all data from Feb 2018 onwards (unless a subsequent partition is added in the future)\r\n);<\/pre>\n<h2>Partition Scheme<\/h2>\n<p>Next, we create the scheme to tell SQL Server how to organise the data on the disk(s):<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">--------------------------------------------------------------------------------\r\n-- Partition Scheme\r\n--------------------------------------------------------------------------------\r\nCREATE PARTITION SCHEME [FactTable_PS]\r\n    AS PARTITION [FactTable_PF]\r\nALL TO ([primary])\r\n;<\/pre>\n<h2>Partitioned Table Definition<\/h2>\n<p>Like with partitioning an existing table, we use a separate CLUSTERED INDEX definition to apply the scheme and function. What is different here, is the application of a primary key which is explicitly UNCLUSTERED.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">--------------------------------------------------------------------------------\r\n-- Create Table using its own PK:\r\n-- NONCLUSTERED is important, without it the data remains unpartitioned and only the index is partitioned\r\n--------------------------------------------------------------------------------\r\nCREATE TABLE [dbo].[FactTable_partitioned_different_pk]\r\n(\r\n    [surrogate_key] BIGINT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,\r\n    [Date]      DATE,\r\n    [attribute] VARCHAR(100),\r\n    [metric]    BIGINT\r\n)\r\n;\r\n\r\nCREATE CLUSTERED\r\n INDEX DK_FactTable_partitioned_different_pk\r\n    ON [dbo].[FactTable_partitioned_different_pk]\r\n      ([Date])\r\n    ON [FactTable_PS] ([Date])\r\n;<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In my series on partitioning, I&#8217;ve looked at creating a fact table partitioned by date. But I recently came across a case where I&#8217;d got data which had a date which should be used for the partition, however there was also a natural key in the data which should be maintained as a primary key.&hellip;<\/p>\n<p class=\"read-more\"><a class=\"readmore-btn\" href=\"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/\">Read More<span class=\"screen-reader-text\">  Read More<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4],"tags":[24],"class_list":["post-281","post","type-post","status-publish","format-standard","hentry","category-databases","category-sql-server","tag-partition"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Partitions in SQL Server: Partitioning a Table on a non Primary Key Column - Rows Across The Lake<\/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:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Partitions in SQL Server: Partitioning a Table on a non Primary Key Column - Rows Across The Lake\" \/>\n<meta property=\"og:description\" content=\"In my series on partitioning, I&#8217;ve looked at creating a fact table partitioned by date. But I recently came across a case where I&#8217;d got data which had a date which should be used for the partition, however there was also a natural key in the data which should be maintained as a primary key.&hellip;Read More Read More\" \/>\n<meta property=\"og:url\" content=\"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/\" \/>\n<meta property=\"og:site_name\" content=\"Rows Across The Lake\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-20T18:00:38+00:00\" \/>\n<meta name=\"author\" content=\"david\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:site\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"david\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated 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:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/04\\\/20\\\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/04\\\/20\\\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\\\/\"},\"author\":{\"name\":\"david\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"headline\":\"Partitions in SQL Server: Partitioning a Table on a non Primary Key Column\",\"datePublished\":\"2019-04-20T18:00:38+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/04\\\/20\\\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\\\/\"},\"wordCount\":317,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"keywords\":[\"partition\"],\"articleSection\":[\"Databases\",\"SQL Server\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/04\\\/20\\\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/04\\\/20\\\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\\\/\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/04\\\/20\\\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\\\/\",\"name\":\"Partitions in SQL Server: Partitioning a Table on a non Primary Key Column - Rows Across The Lake\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\"},\"datePublished\":\"2019-04-20T18:00:38+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/04\\\/20\\\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/04\\\/20\\\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/04\\\/20\\\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Partitions in SQL Server: Partitioning a Table on a non Primary Key Column\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\",\"name\":\"Rows Across The Lake\",\"description\":\"Data &amp; Databases\",\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\",\"name\":\"david\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"caption\":\"david\"},\"logo\":{\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Partitions in SQL Server: Partitioning a Table on a non Primary Key Column - Rows Across The Lake","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:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/","og_locale":"en_GB","og_type":"article","og_title":"Partitions in SQL Server: Partitioning a Table on a non Primary Key Column - Rows Across The Lake","og_description":"In my series on partitioning, I&#8217;ve looked at creating a fact table partitioned by date. But I recently came across a case where I&#8217;d got data which had a date which should be used for the partition, however there was also a natural key in the data which should be maintained as a primary key.&hellip;Read More Read More","og_url":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/","og_site_name":"Rows Across The Lake","article_published_time":"2019-04-20T18:00:38+00:00","author":"david","twitter_card":"summary_large_image","twitter_creator":"@d_roman_h","twitter_site":"@d_roman_h","twitter_misc":{"Written by":"david","Estimated reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/#article","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/"},"author":{"name":"david","@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"headline":"Partitions in SQL Server: Partitioning a Table on a non Primary Key Column","datePublished":"2019-04-20T18:00:38+00:00","mainEntityOfPage":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/"},"wordCount":317,"commentCount":0,"publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"keywords":["partition"],"articleSection":["Databases","SQL Server"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/","url":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/","name":"Partitions in SQL Server: Partitioning a Table on a non Primary Key Column - Rows Across The Lake","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/#website"},"datePublished":"2019-04-20T18:00:38+00:00","breadcrumb":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/04\/20\/partitions-in-sql-server-partitioning-a-table-on-a-non-primary-key-column\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/datablog.roman-halliday.com\/"},{"@type":"ListItem","position":2,"name":"Partitions in SQL Server: Partitioning a Table on a non Primary Key Column"}]},{"@type":"WebSite","@id":"https:\/\/datablog.roman-halliday.com\/#website","url":"https:\/\/datablog.roman-halliday.com\/","name":"Rows Across The Lake","description":"Data &amp; Databases","publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/datablog.roman-halliday.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b","name":"david","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","caption":"david"},"logo":{"@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g"}}]}},"_links":{"self":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/281","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/comments?post=281"}],"version-history":[{"count":2,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/281\/revisions"}],"predecessor-version":[{"id":298,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/281\/revisions\/298"}],"wp:attachment":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/media?parent=281"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/categories?post=281"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/tags?post=281"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}