{"id":4179,"date":"2024-09-27T08:25:21","date_gmt":"2024-09-27T00:25:21","guid":{"rendered":"https:\/\/five.co\/?p=4179"},"modified":"2026-05-07T08:49:10","modified_gmt":"2026-05-07T00:49:10","slug":"sql-server-partitioning","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/sql-server-partitioning\/","title":{"rendered":"SQL Server Partitioning: Practical Guide"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">SQL Server partitioning is a great for managing large databases and boosting query performance. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In this guide, we&#8217;ll dive into the nuts and bolts of partitioning, explore its benefits, and walk through real-world examples to help you implement this technique effectively.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">What is SQL Server Partitioning?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">At its core, SQL Server partitioning is about dividing large tables and indexes into smaller, more manageable pieces. Instead of storing all data in a single unit, partitioning spreads it across multiple filegroups based on a partitioning scheme.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s look at a simple example:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Create a partition function\nCREATE PARTITION FUNCTION &#091;PF_OrderDate&#093;(datetime) AS RANGE RIGHT FOR VALUES\n('2022-01-01', '2023-01-01', '2024-01-01')\n\n-- Create a partition scheme\nCREATE PARTITION SCHEME &#091;PS_OrderDate&#093; AS PARTITION &#091;PF_OrderDate&#093; \nTO (&#091;FG2022&#093;, &#091;FG2023&#093;, &#091;FG2024&#093;, &#091;FG2025&#093;)\n\n-- Create a partitioned table\nCREATE TABLE Orders (\n    OrderID INT PRIMARY KEY,\n    OrderDate DATETIME,\n    CustomerID INT,\n    TotalAmount DECIMAL(10,2)\n) ON &#091;PS_OrderDate&#093;(OrderDate)<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create a partition function<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FUNCTION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;PF_OrderDate&#093;<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">datetime<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">RANGE<\/span><span style=\"color: #ABB2BF\"> RIGHT <\/span><span style=\"color: #C678DD\">FOR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VALUES<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;2022-01-01&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;2023-01-01&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;2024-01-01&#39;<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create a partition scheme<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> SCHEME <\/span><span style=\"color: #E06C75\">&#091;PS_OrderDate&#093;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;PF_OrderDate&#093;<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">TO<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #E06C75\">&#091;FG2022&#093;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">&#091;FG2023&#093;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">&#091;FG2024&#093;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">&#091;FG2025&#093;<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create a partitioned table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">Orders<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    OrderID <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    OrderDate <\/span><span style=\"color: #C678DD\">DATETIME<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CustomerID <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    TotalAmount <\/span><span style=\"color: #C678DD\">DECIMAL<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\">,<\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;PS_OrderDate&#093;<\/span><span style=\"color: #ABB2BF\">(OrderDate)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">In this example, we&#8217;ve created a partition function that splits data based on the OrderDate. The partition scheme then maps these logical partitions to physical filegroups. Finally, we create the Orders table using this partitioning scheme.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Why Use SQL Server Partitioning?<\/h2>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Improved Query Performance<\/strong>: By dividing data, SQL Server can quickly eliminate irrelevant partitions during query execution, significantly reducing I\/O operations.<\/li>\n\n\n\n<li><strong>Easier Data Management<\/strong>: Need to archive old data? With partitioning, you can efficiently move or delete entire partitions without affecting the rest of the table.<\/li>\n\n\n\n<li><strong>Faster Data Loading<\/strong>: Bulk insert operations can be parallelized across partitions, speeding up data ingestion.<\/li>\n\n\n\n<li><strong>Enhanced Availability<\/strong>: Partitions can be backed up and restored independently, allowing for more flexible maintenance strategies.<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Real-World Scenario: E-commerce Order Management<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s consider an e-commerce platform that processes millions of orders annually. As the business grows, querying and managing the ever-expanding Orders table becomes increasingly challenging.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s how we might implement partitioning for this scenario:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Create filegroups for each year\nALTER DATABASE ECommerceDB ADD FILEGROUP &#091;FG2022&#093;\nALTER DATABASE ECommerceDB ADD FILEGROUP &#091;FG2023&#093;\nALTER DATABASE ECommerceDB ADD FILEGROUP &#091;FG2024&#093;\nALTER DATABASE ECommerceDB ADD FILEGROUP &#091;FG2025&#093;\n\n-- Add files to filegroups\nALTER DATABASE ECommerceDB ADD FILE \n( NAME = N'OrderData2022', FILENAME = N'C:\\Data\\OrderData2022.ndf' )\nTO FILEGROUP &#091;FG2022&#093;\n\n-- Repeat for other years...\n\n-- Create partition function and scheme (as shown earlier)\n\n-- Create the partitioned Orders table\nCREATE TABLE Orders (\n    OrderID INT IDENTITY(1,1) PRIMARY KEY,\n    OrderDate DATETIME NOT NULL,\n    CustomerID INT NOT NULL,\n    TotalAmount DECIMAL(10,2) NOT NULL,\n    Status VARCHAR(20) NOT NULL\n) ON &#091;PS_OrderDate&#093;(OrderDate)\n\n-- Create a non-clustered index on CustomerID\nCREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON Orders(CustomerID)\nON &#091;PS_OrderDate&#093;(OrderDate)<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create filegroups for each year<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATABASE<\/span><span style=\"color: #ABB2BF\"> ECommerceDB <\/span><span style=\"color: #C678DD\">ADD<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FILEGROUP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;FG2022&#093;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATABASE<\/span><span style=\"color: #ABB2BF\"> ECommerceDB <\/span><span style=\"color: #C678DD\">ADD<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FILEGROUP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;FG2023&#093;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATABASE<\/span><span style=\"color: #ABB2BF\"> ECommerceDB <\/span><span style=\"color: #C678DD\">ADD<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FILEGROUP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;FG2024&#093;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATABASE<\/span><span style=\"color: #ABB2BF\"> ECommerceDB <\/span><span style=\"color: #C678DD\">ADD<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FILEGROUP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;FG2025&#093;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Add files to filegroups<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATABASE<\/span><span style=\"color: #ABB2BF\"> ECommerceDB <\/span><span style=\"color: #C678DD\">ADD<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FILE<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">( <\/span><span style=\"color: #C678DD\">NAME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">N&#39;OrderData2022&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #C678DD\">FILENAME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">N&#39;C:\\Data\\OrderData2022.ndf&#39;<\/span><span style=\"color: #ABB2BF\"> )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">TO<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FILEGROUP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;FG2022&#093;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Repeat for other years...<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create partition function and scheme (as shown earlier)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create the partitioned Orders table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">Orders<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    OrderID <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">IDENTITY<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">,<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    OrderDate <\/span><span style=\"color: #C678DD\">DATETIME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NOT NULL<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CustomerID <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NOT NULL<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    TotalAmount <\/span><span style=\"color: #C678DD\">DECIMAL<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\">,<\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">NOT NULL<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">Status<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">20<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">NOT NULL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;PS_OrderDate&#093;<\/span><span style=\"color: #ABB2BF\">(OrderDate)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Create a non-clustered index on CustomerID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NONCLUSTERED<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INDEX<\/span><span style=\"color: #ABB2BF\"> IX_Orders_CustomerID <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> Orders(CustomerID)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;PS_OrderDate&#093;<\/span><span style=\"color: #ABB2BF\">(OrderDate)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">With this setup, queries filtering on OrderDate will benefit from partition elimination. For example:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT * FROM Orders \nWHERE OrderDate >= '2023-06-01' AND OrderDate &lt; '2023-07-01'<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Orders <\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> OrderDate <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-06-01&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> OrderDate <\/span><span style=\"color: #56B6C2\">&lt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-07-01&#39;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query will only scan the relevant partition(s) in the FG2023 filegroup, significantly reducing I\/O and improving performance.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Implementing Sliding Window Partitioning<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">For our e-commerce scenario, we might want to implement a sliding window strategy to maintain the most recent data online while archiving older data. Here&#8217;s how we can achieve this:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Step 1: Prepare the next partition\nALTER DATABASE ECommerceDB ADD FILEGROUP &#091;FG2026&#093;\nALTER DATABASE ECommerceDB ADD FILE \n( NAME = N'OrderData2026', FILENAME = N'C:\\Data\\OrderData2026.ndf' )\nTO FILEGROUP &#091;FG2026&#093;\n\n-- Step 2: Modify the partition scheme to include the new filegroup\nALTER PARTITION SCHEME &#091;PS_OrderDate&#093; NEXT USED &#091;FG2026&#093;\n\n-- Step 3: Split the rightmost partition\nALTER PARTITION FUNCTION &#091;PF_OrderDate&#093;() \nSPLIT RANGE ('2025-01-01')\n\n-- Step 4: Move or archive data from the leftmost partition\n-- (Assuming we want to keep 3 years of data online)\nCREATE TABLE ArchivedOrders (\n    -- Same structure as Orders table\n) ON &#091;FG2022&#093;\n\nINSERT INTO ArchivedOrders\nSELECT * FROM Orders\nWHERE $PARTITION.&#091;PF_OrderDate&#093;(OrderDate) = 1\n\n-- Step 5: Remove data from the original table\nDELETE FROM Orders\nWHERE $PARTITION.&#091;PF_OrderDate&#093;(OrderDate) = 1\n\n-- Step 6: Merge the empty partition\nALTER PARTITION FUNCTION &#091;PF_OrderDate&#093;() \nMERGE RANGE ('2022-01-01')\n\n-- Step 7: Remove the old filegroup (optional)\nALTER DATABASE ECommerceDB REMOVE FILE OrderData2022\nALTER DATABASE ECommerceDB REMOVE FILEGROUP &#091;FG2022&#093;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Step 1: Prepare the next partition<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATABASE<\/span><span style=\"color: #ABB2BF\"> ECommerceDB <\/span><span style=\"color: #C678DD\">ADD<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FILEGROUP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;FG2026&#093;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATABASE<\/span><span style=\"color: #ABB2BF\"> ECommerceDB <\/span><span style=\"color: #C678DD\">ADD<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FILE<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">( <\/span><span style=\"color: #C678DD\">NAME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">N&#39;OrderData2026&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #C678DD\">FILENAME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">N&#39;C:\\Data\\OrderData2026.ndf&#39;<\/span><span style=\"color: #ABB2BF\"> )<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">TO<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FILEGROUP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;FG2026&#093;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Step 2: Modify the partition scheme to include the new filegroup<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> SCHEME <\/span><span style=\"color: #E06C75\">&#091;PS_OrderDate&#093;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NEXT<\/span><span style=\"color: #ABB2BF\"> USED <\/span><span style=\"color: #E06C75\">&#091;FG2026&#093;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Step 3: Split the rightmost partition<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FUNCTION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;PF_OrderDate&#093;<\/span><span style=\"color: #ABB2BF\">() <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">SPLIT <\/span><span style=\"color: #C678DD\">RANGE<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #98C379\">&#39;2025-01-01&#39;<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Step 4: Move or archive data from the leftmost partition<\/span><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- (Assuming we want to keep 3 years of data online)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">ArchivedOrders<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Same structure as Orders table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;FG2022&#093;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> ArchivedOrders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> $<\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #E06C75\">&#091;PF_OrderDate&#093;<\/span><span style=\"color: #ABB2BF\">(OrderDate) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Step 5: Remove data from the original table<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DELETE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> $<\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #E06C75\">&#091;PF_OrderDate&#093;<\/span><span style=\"color: #ABB2BF\">(OrderDate) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Step 6: Merge the empty partition<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FUNCTION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;PF_OrderDate&#093;<\/span><span style=\"color: #ABB2BF\">() <\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">MERGE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">RANGE<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #98C379\">&#39;2022-01-01&#39;<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Step 7: Remove the old filegroup (optional)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATABASE<\/span><span style=\"color: #ABB2BF\"> ECommerceDB <\/span><span style=\"color: #C678DD\">REMOVE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FILE<\/span><span style=\"color: #ABB2BF\"> OrderData2022<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DATABASE<\/span><span style=\"color: #ABB2BF\"> ECommerceDB <\/span><span style=\"color: #C678DD\">REMOVE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FILEGROUP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;FG2022&#093;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This process can be automated using SQL Server Agent jobs to run periodically, ensuring your partitioning scheme stays current with your data retention policies.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Optimizing Queries for Partitioned Tables<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">While partitioning can significantly improve performance, it&#8217;s crucial to write queries that take advantage of this structure. Here are some tips:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Use the partitioning column in WHERE clauses<\/strong>: This allows SQL Server to use partition elimination.<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Good: Uses partition elimination\nSELECT * FROM Orders WHERE OrderDate >= '2023-01-01' AND OrderDate &lt; '2024-01-01'\n\n-- Bad: Full table scan\nSELECT * FROM Orders WHERE TotalAmount > 1000<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Good: Uses partition elimination<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Orders <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> OrderDate <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-01-01&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> OrderDate <\/span><span style=\"color: #56B6C2\">&lt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2024-01-01&#39;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Bad: Full table scan<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Orders <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> TotalAmount <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1000<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li><strong>Be cautious with JOINs<\/strong>: If joining partitioned tables, ensure the partitioning columns are used in join conditions to maintain partition-aligned operations.<\/li>\n\n\n\n<li><strong>Consider partition-aligned indexes<\/strong>: Creating indexes that include the partitioning column can improve query performance.<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>CREATE NONCLUSTERED INDEX IX_Orders_Status_OrderDate ON Orders(Status, OrderDate)\nON &#091;PS_OrderDate&#093;(OrderDate)<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NONCLUSTERED<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INDEX<\/span><span style=\"color: #ABB2BF\"> IX_Orders_Status_OrderDate <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> Orders(<\/span><span style=\"color: #C678DD\">Status<\/span><span style=\"color: #ABB2BF\">, OrderDate)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">&#091;PS_OrderDate&#093;<\/span><span style=\"color: #ABB2BF\">(OrderDate)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<ol start=\"4\" class=\"wp-block-list\">\n<li><strong>Use partition-aware maintenance<\/strong>: When rebuilding indexes or updating statistics, consider doing so on a per-partition basis.<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Rebuild index for a specific partition\nALTER INDEX IX_Orders_Status_OrderDate ON Orders \nREBUILD PARTITION = 2<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Rebuild index for a specific partition<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ALTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INDEX<\/span><span style=\"color: #ABB2BF\"> IX_Orders_Status_OrderDate <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> Orders <\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">REBUILD<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">2<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-text-align-center wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\"><strong style=\"background-color: rgb(69, 136, 216); color: rgb(241, 235, 218); font-size: calc(12px + 0.390625vw); font-weight: bold;\">SQL Server Partitioning with a Rapid Database Builder<\/strong><\/mark><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When working with SQL, executing efficient queries is straightforward, but sometimes more complex operations, like managing large datasets using SQL Server partitioning clauses, require extra attention.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is where rapid database builders like Five come into play. Five allows you to connect to your existing SQL Server database, enabling you to build a web application on top of your current data infrastructure.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five allows you to create interactive forms, tables, dynamic charts, comprehensive PDF reports, and dashboards that are automatically generated based on your database schema. This makes it easier to visualize and analyze the results of queries, including those that use SQL Server&#8217;s partitioning features.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Additionally, Five lets you write custom JavaScript and TypeScript functions, providing the flexibility to implement custom business logic that can interact with your data. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Once your application is ready, Five makes deployment easy with just a few clicks, allowing you to deploy your application connected to your SQL Server database to a secure, scalable cloud infrastructure. This lets you focus on development while Five handles the complexities of cloud deployment and infrastructure management.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;re serious about using SQL efficiently, give Five a try.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your web application connected to your SQL Server database today.<\/a><\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Build Your Database In 3 Steps<\/strong><br \/><span style=\"font-size: 14pt;\">Start Developing Today<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\" data-wplink-edit=\"true\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Instant Access<\/strong><\/button><br \/><\/a><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Monitoring Partition Usage and Performance<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To ensure your partitioning strategy is effective, regular monitoring is essential. Here are some useful queries:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Check row count per partition\nSELECT \n    p.partition_number,\n    p.rows,\n    au.total_pages,\n    au.used_pages,\n    OBJECT_NAME(p.object_id) AS TableName,\n    fg.name AS FileGroupName\nFROM sys.partitions p\nJOIN sys.allocation_units au ON p.partition_id = au.container_id\nJOIN sys.filegroups fg ON au.data_space_id = fg.data_space_id\nWHERE OBJECT_NAME(p.object_id) = 'Orders'\nORDER BY p.partition_number\n\n-- Check partition elimination in query plans\nSELECT qp.query_plan\nFROM sys.dm_exec_cached_plans AS cp\nCROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp\nWHERE qp.query_plan.exist('\/\/RelOp&#091;@PhysicalOp=\"Clustered Index Scan\" or @PhysicalOp=\"Clustered Index Seek\"&#093;\/*&#091;local-name()=\"RunTimePartitionSummary\"&#093;')=1<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Check row count per partition<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">partition_number<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">rows<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">au<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">total_pages<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">au<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">used_pages<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">OBJECT_NAME<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">object_id<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> TableName,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #D19A66\">fg<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">name<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> FileGroupName<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">sys<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">partitions<\/span><span style=\"color: #ABB2BF\"> p<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">sys<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">allocation_units<\/span><span style=\"color: #ABB2BF\"> au <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">partition_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">au<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">container_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">sys<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">filegroups<\/span><span style=\"color: #ABB2BF\"> fg <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">au<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">data_space_id<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">fg<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">data_space_id<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">OBJECT_NAME<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">object_id<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Orders&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">p<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">partition_number<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Check partition elimination in query plans<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">qp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">query_plan<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">sys<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">dm_exec_cached_plans<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> cp<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CROSS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">APPLY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">sys<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">dm_exec_query_plan<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">cp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">plan_handle<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> qp<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">qp<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">query_plan<\/span><span style=\"color: #ABB2BF\">.exist(<\/span><span style=\"color: #98C379\">&#39;\/\/RelOp&#091;@PhysicalOp=&quot;Clustered Index Scan&quot; or @PhysicalOp=&quot;Clustered Index Seek&quot;&#093;\/*&#091;local-name()=&quot;RunTimePartitionSummary&quot;&#093;&#39;<\/span><span style=\"color: #ABB2BF\">)<\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #D19A66\">1<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">These queries help you understand how your data is distributed across partitions and whether your queries are effectively using partition elimination.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SQL Server partitioning is a technique for managing large-scale data and optimizing query performance. By dividing data into smaller, more manageable chunks, you can significantly improve database operations, from querying to maintenance.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Remember, partitioning isn&#8217;t a one-size-fits-all solution. It&#8217;s most effective for large tables (typically over 1 million rows) and scenarios where data has a natural partitioning key (like date ranges in our e-commerce example). Always test thoroughly in a non-production environment before implementing partitioning in your production systems.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\" target=\"_blank\" rel=\"noreferrer noopener\">Sign up for free access to Five\u2019s online development environment and start building your web application connected to your SQL Server database today.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server partitioning is a great for managing large databases and boosting query performance. In this guide, we&#8217;ll dive into the nuts and bolts of partitioning, explore its benefits, and walk through real-world examples to help you implement this technique effectively. What is SQL Server Partitioning? At its core, SQL Server partitioning is about dividing [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4180,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-4179","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Server-Partitioning-Practical-Guide.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Server-Partitioning-Practical-Guide-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Server-Partitioning-Practical-Guide-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Server-Partitioning-Practical-Guide-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Server-Partitioning-Practical-Guide-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Server-Partitioning-Practical-Guide.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-SQL-Server-Partitioning-Practical-Guide.jpg",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"SQL Server partitioning is a great for managing large databases and boosting query performance. In this guide, we&#8217;ll dive into the nuts and bolts of partitioning, explore its benefits, and walk through real-world examples to help you implement this technique effectively. What is SQL Server Partitioning? At its core, SQL Server partitioning is about dividing&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=4179"}],"version-history":[{"count":1,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4179\/revisions"}],"predecessor-version":[{"id":17834,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4179\/revisions\/17834"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4180"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}