I want to demonstrate a simple, yet common style partitioned fact table. I’ll also include here some handy bits of SQL to help along the way.<\/p>\n
I started looking at partitions, and the syntax behind the commands in some previous posts. If you are new to partitioning in SQL Server,it’s worth checking them out. If you just want to create a new partitioned fact table, read ahead.<\/p>\n
For this article, I’ll have a very generic and (for the purpose of demonstration) small fact table. It has a date (on which we want to partition the data) and some extra attributes and metrics. It represents a simple key value pair for attribute values for each day:<\/p>\n
CREATE TABLE [dbo].[FactTable]\r\n(\r\n [Date] DATE,\r\n [Attribute] VARCHAR(100),\r\n [Metric] BIGINT\r\n);<\/pre>\nDate is used here to partition as it has the advantages of:<\/p>\n
\n
- Simplifying insertions (new data for each period is added to a new partition).<\/li>\n
- Deletions (in the event of reprocessing) can be done easily\/quickly. Especially post 2016, where a partition can be truncated.<\/li>\n
- When querying fact tables, metrics are frequently rolled up for date periods, or only specific date ranges are required.<\/li>\n<\/ol>\n
Components of a Partitioned Table<\/h2>\n
\n
- 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
- Partition Scheme<\/strong>: The definition of how a 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
- Table & Index Definition<\/strong>: The last part is the definition of the table, including the partition scheme<\/em> to use, and the index with which to apply it.<\/li>\n<\/ol>\n
Creating The Partitioned Table<\/h1>\n
Partition Function<\/h2>\n
For this dataset, I’m going to add a new partition for each month (functions can be for any time frame you chose):<\/p>\n
--------------------------------------------------------------------------------\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'2018-01-01T00:00:00', -- This will contain all data for 2018 Jan onwards (until the next partition). All data before then goes into a 'catch all' partition\r\n N'2018-02-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>\nPartition Scheme<\/h2>\n
Next, we create the scheme to tell SQL Server how to organise the data on the disk(s):<\/p>\n
--------------------------------------------------------------------------------\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>\nTable & Index<\/h2>\n
A table should always (OK, so there might be exceptions) have a clustered index. Either as an explicit primary key, or just a plain clustered index. A fact table is no different. The clustered index is where the performance can be lifted with partitions.<\/p>\n
Below I’m creating two variations on the fact table, one with a classic Primary Key, the other using a Clustered Index. Both will have the same result of partitioning the data using the scheme defined above.<\/p>\n
--------------------------------------------------------------------------------\r\n-- Fact Table\r\n-- Using PK\r\n--------------------------------------------------------------------------------\r\nCREATE TABLE [dbo].[FactTable_1]\r\n(\r\n [Date] DATE,\r\n [Attribute] VARCHAR(100),\r\n [Metric] BIGINT,\r\n CONSTRAINT [PK_FactTable] PRIMARY KEY ([Date], [Attribute]) -- optional PK Constraint\r\n)\r\nON [FactTable_PS] ([Date])\r\nWITH -- Optional compression, worth it on large tables\r\n( DATA_COMPRESSION = PAGE\r\n)\r\n;\r\n\r\n--------------------------------------------------------------------------------\r\n-- Fact Table\r\n-- Using CLUSTERED INDEX\r\n--------------------------------------------------------------------------------\r\nCREATE TABLE [dbo].[FactTable_2]\r\n(\r\n [Date] DATE,\r\n [Attribute] VARCHAR(100),\r\n [Metric] BIGINT\r\n)\r\nON [FactTable_PS] ([Date])\r\nWITH -- Optional compression, worth it on large tables\r\n( DATA_COMPRESSION = PAGE\r\n)\r\n;\r\n\r\nCREATE CLUSTERED\r\n INDEX [CIDX_FactTable_2]\r\n ON [dbo].[FactTable_2]\r\n ([Date])\r\n ON [FactTable_PS] ([Date])\r\n;<\/pre>\nAdding More Partitions<\/h2>\n
So, from the definition of the function (and therefore scheme) above, we can see that only two partitions have been declared. As we are using the same time frame for each partition (not a requirement, and there are arguments for having different durations for different partitions), We can programmatically add new partitions:<\/p>\n
--------------------------------------------------------------------------------\r\n-- Add New Partitions (for date range)\r\n-- From last date in table, up to latest\r\n--------------------------------------------------------------------------------\r\nDECLARE @CurrentDate DATE= GetDate();\r\nDECLARE @ExistingMax DATE=(SELECT MAX(CONVERT(DATE,Value))\r\n FROM sys.partition_functions f\r\n INNER JOIN sys.partition_range_values r \r\n ON f.function_id = r.function_id \r\n WHERE f.name = 'FactTable_PF' -- Function Name\r\n );\r\n\r\nPRINT ' Date Now: ' + CONVERT(VARCHAR(10), @CurrentDate, 120);\r\nPRINT 'Max Partition Date: ' + CONVERT(VARCHAR(10), @ExistingMax, 120);\r\n\r\n\r\nIF @ExistingMax < @CurrentDate\r\nBEGIN\r\n DECLARE @lastDate DATE;\r\n DECLARE @processingDate DATE = @ExistingMax;\r\n\r\n WHILE @processingDate <= @CurrentDate\r\n BEGIN\r\n SET @lastDate = @processingDate;\r\n SET @processingDate = DateAdd(MONTH, 1, DateAdd(month, DateDiff(month, 0, @processingDate), 0)) -- Note: See date processing\r\n\r\n PRINT 'Adding partition for: ' + CONVERT(VARCHAR(10), @processingDate, 120);\r\n\r\n BEGIN TRANSACTION;\r\n\r\n ALTER PARTITION SCHEME FactTable_PS\r\n NEXT USED [PRIMARY]\r\n\r\n ALTER PARTITION FUNCTION FactTable_PF()\r\n SPLIT RANGE (@processingDate)\r\n\r\n COMMIT TRANSACTION;\r\n END;\r\n\r\nEND;\r\n<\/pre>\nTesting Our Fact Table<\/h1>\n
To test our fact table(s), we first generate some dummy data, insert it to te tables, then view the metadata for the partitions.<\/p>\n
Dummy Data<\/h2>\n
--------------------------------------------------------------------------------\r\n-- Create Date List\r\n--------------------------------------------------------------------------------\r\nDECLARE @DateStart DATE = '01-JAN-17';\r\nDECLARE @DateEnd DATE = GetDate();\r\n\r\nWITH number_tbl AS (\r\nSELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL\r\nSELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL\r\nSELECT 8 UNION ALL SELECT 9\r\n), number_sequences AS (\r\nSELECT (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation\r\n FROM number_tbl t1\r\n CROSS JOIN number_tbl t2\r\n CROSS JOIN number_tbl t3\r\n), date_sequence AS (\r\nSELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]\r\n FROM number_sequences\r\n WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd\r\n)\r\nSELECT [TheDate]\r\n INTO #DateList\r\n FROM date_sequence\r\n ORDER BY [TheDate]\r\n;\r\n\r\nCREATE UNIQUE CLUSTERED\r\n INDEX PK_DateList\r\n ON #DateList\r\n ([TheDate])\r\n;\r\n\r\n--------------------------------------------------------------------------------\r\n-- Create Dummy Fact table (we will keep coming back to this)\r\n-- Using cartesian join, giving 4 rows for each date\r\n--------------------------------------------------------------------------------\r\nWITH s_data AS (\r\nSELECT 'Data 1' AS [c_attribute], 100 AS [c_metric] UNION ALL\r\nSELECT 'Data 2' AS [c_attribute], 200 AS [c_metric] UNION ALL\r\nSELECT 'Data 3' AS [c_attribute], 300 AS [c_metric] UNION ALL\r\nSELECT 'Data 4' AS [c_attribute], 400 AS [c_metric]\r\n)\r\nSELECT [TheDate],\r\n [c_attribute],\r\n [c_metric]\r\n INTO #FactTable\r\n FROM #DateList,\r\n s_data\r\n;\r\n\r\nCREATE CLUSTERED\r\n INDEX SK_DateList\r\n ON #FactTable\r\n ([TheDate])\r\n;\r\n\r\n--------------------------------------------------------------------------------\r\n-- Data Inserts\r\n--------------------------------------------------------------------------------\r\nINSERT\r\n INTO [dbo].[FactTable_1]\r\nSELECT *\r\n FROM #FactTable\r\n;\r\n\r\nINSERT\r\n INTO [dbo].[FactTable_2]\r\nSELECT *\r\n FROM #FactTable\r\n;\r\n<\/pre>\nView Partitions & Usage<\/h2>\n
This is a handy SQL statement to view lots of useful information about partitions:<\/p>\n
--------------------------------------------------------------------------------\r\n-- View Partitions and usage\r\n--------------------------------------------------------------------------------\r\nSELECT OBJECT_NAME(p.[object_id]) AS [Table_Name],\r\n --p.[object_id],\r\n --p.[partition_id],\r\n p.[partition_number],\r\n p.[rows] AS [Partition_rows],\r\n p.[data_compression_desc] AS [Partition_data_compression],\r\n --i.[index_id], -- Linked by [object_id]\r\n i.[name] AS [Index_Name],\r\n --i.[type],\r\n i.[type_desc] AS [Index_Type],\r\n --i.[is_unique],\r\n --i.[is_primary_key],\r\n --i.[is_unique_constraint],\r\n --i.[data_space_id], -- Link to scheme\r\n s.[name] AS [Scheme_Name],\r\n --f.[function_id],\r\n f.[name] AS [Function_Name],\r\n --f.[create_date] AS [Function_create_date],\r\n --f.[modify_date] AS [Function_modify_date],\r\n --rv.[boundary_id], -- Link to partition_id\r\n lv.[value] AS [Lower_Value],\r\n rv.[value] AS [Upper_Value]\r\n FROM sys.partitions p\r\n INNER JOIN sys.objects o\r\n ON o.[object_id] = p.[object_id]\r\n --INNER JOIN sys.allocation_units a\r\n -- ON p.[hobt_id] = a.[container_id]\r\n LEFT JOIN sys.indexes i\r\n ON i.[object_id] = p.[object_id]\r\n AND i.[index_id] = p.[index_id]\r\n LEFT JOIN sys.partition_schemes s\r\n ON s.[data_space_id] = i.[data_space_id]\r\n LEFT JOIN sys.partition_functions f\r\n ON f.[function_id] = s.[function_id]\r\n LEFT JOIN sys.partition_range_values rv\r\n ON rv.[function_id] = f.[function_id]\r\n AND rv.[boundary_id] = p.[partition_number]\r\n LEFT JOIN sys.partition_range_values lv\r\n ON lv.[function_id] = f.[function_id]\r\n AND lv.[boundary_id] = p.[partition_number] - 1\r\n WHERE 1=1\r\n AND( p.[object_id] = OBJECT_ID('dbo.FactTable_1') -- Filter for an object\r\n OR p.[object_id] = OBJECT_ID('dbo.FactTable_2')\r\n )\r\n --AND o.[name] LIKE 'FactTable%' -- Alternative filter\r\n ORDER BY o.[name],\r\n i.[name],\r\n p.[partition_number]\r\n;<\/pre>\n<\/p>\n","protected":false},"excerpt":{"rendered":"
I want to demonstrate a simple, yet common style partitioned fact table. I’ll also include here some handy bits of SQL to help along the way.<\/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":[12,24,8],"class_list":["post-276","post","type-post","status-publish","format-standard","hentry","category-databases","category-sql-server","tag-date","tag-partition","tag-t-sql"],"yoast_head":"\n
Partitions in SQL Server: Creating a Partitioned Fact Table (by date) - 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\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/\" \/>\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: Creating a Partitioned Fact Table (by date) - Rows Across The Lake\" \/>\n<meta property=\"og:description\" content=\"I want to demonstrate a simple, yet common style partitioned fact table. I'll also include here some handy bits of SQL to help along the way.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/\" \/>\n<meta property=\"og:site_name\" content=\"Rows Across The Lake\" \/>\n<meta property=\"article:published_time\" content=\"2019-03-31T18:50:50+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=\"7 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\\\/03\\\/31\\\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/03\\\/31\\\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\\\/\"},\"author\":{\"name\":\"david\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"headline\":\"Partitions in SQL Server: Creating a Partitioned Fact Table (by date)\",\"datePublished\":\"2019-03-31T18:50:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/03\\\/31\\\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\\\/\"},\"wordCount\":555,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"keywords\":[\"date\",\"partition\",\"T-SQL\"],\"articleSection\":[\"Databases\",\"SQL Server\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/03\\\/31\\\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/03\\\/31\\\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\\\/\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/03\\\/31\\\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\\\/\",\"name\":\"Partitions in SQL Server: Creating a Partitioned Fact Table (by date) - Rows Across The Lake\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\"},\"datePublished\":\"2019-03-31T18:50:50+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/03\\\/31\\\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/03\\\/31\\\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2019\\\/03\\\/31\\\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Partitions in SQL Server: Creating a Partitioned Fact Table (by date)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\",\"name\":\"Rows Across The Lake\",\"description\":\"Data & 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: Creating a Partitioned Fact Table (by date) - 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\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/","og_locale":"en_GB","og_type":"article","og_title":"Partitions in SQL Server: Creating a Partitioned Fact Table (by date) - Rows Across The Lake","og_description":"I want to demonstrate a simple, yet common style partitioned fact table. I'll also include here some handy bits of SQL to help along the way.","og_url":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/","og_site_name":"Rows Across The Lake","article_published_time":"2019-03-31T18:50:50+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":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/#article","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/"},"author":{"name":"david","@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"headline":"Partitions in SQL Server: Creating a Partitioned Fact Table (by date)","datePublished":"2019-03-31T18:50:50+00:00","mainEntityOfPage":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/"},"wordCount":555,"commentCount":2,"publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"keywords":["date","partition","T-SQL"],"articleSection":["Databases","SQL Server"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2019\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/","url":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/","name":"Partitions in SQL Server: Creating a Partitioned Fact Table (by date) - Rows Across The Lake","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/#website"},"datePublished":"2019-03-31T18:50:50+00:00","breadcrumb":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2019\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/03\/31\/partitions-in-sql-server-creating-a-partitioned-fact-table-by-date\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/datablog.roman-halliday.com\/"},{"@type":"ListItem","position":2,"name":"Partitions in SQL Server: Creating a Partitioned Fact Table (by date)"}]},{"@type":"WebSite","@id":"https:\/\/datablog.roman-halliday.com\/#website","url":"https:\/\/datablog.roman-halliday.com\/","name":"Rows Across The Lake","description":"Data & 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\/276","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=276"}],"version-history":[{"count":2,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/276\/revisions"}],"predecessor-version":[{"id":295,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/276\/revisions\/295"}],"wp:attachment":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/media?parent=276"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/categories?post=276"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/tags?post=276"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}