{"id":9274,"date":"2022-08-02T07:06:00","date_gmt":"2022-08-02T05:06:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=9274"},"modified":"2022-08-02T16:48:11","modified_gmt":"2022-08-02T14:48:11","slug":"create-sql-server-partition","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/","title":{"rendered":"Create a SQL Server partition"},"content":{"rendered":"\n

How to create a table partition in SQL Server? Let’s consider a large table with Sales data. So, this table have a column that stores the year of the sale and the table stores millions of lines. <\/p>\n\n\n\n

Let’s consider also that some business reports read this data to display yearly totals and compares them to the previous year totals. It is called a year over year comparison.<\/p>\n\n\n\n

Scripts to create a SQL Server partition step by step<\/h2>\n\n\n\n

To start, let’s use this sales table as an example for the partition creation. <\/p>\n\n\n\n

CREATE TABLE [dbo].[SALES]\n(\n\t[Year] INT,\n\t[MonthName] NVARCHAR(50), -- alphanumeric\n\t[MonthCurrent] BIT, -- Boolean, 0 or 1 , false \/ true\n\t[NumberMonth] TINYINT, -- very small integer, from 0 to 255\n\t[EmployeeNumber] SMALLINT, -- small integer, minimum -2^15, maximum 2^15\n\t[NumberOfClients] INTEGER, -- integer, minimum -2^31, maximum 2^31\n\t[NumberOfSales] BIGINT, -- big integer, minimum: -2^63, maximum 2^63\n\t[Amount_ET] NUMERIC(15,5), -- numeric, 15 digits, with 5 after the comma\n\t[Amount_IT] DECIMAL(15,5) -- decimal, 15 digits, with 5 after the comma\n);\nGO\n<\/pre>\n\n\n\n
To clarify, the partitioning of a table happens with four main objects and decisions to take. The main decision is to choose the partitioning column.<\/p>\n\n\n\n
  1. First step is to choose the partitioning column<\/a><\/strong><\/li>
  2. Second step is to create a partition function<\/a><\/strong><\/li>
  3. Third step is to create the necessary file groups<\/a><\/strong><\/li>
  4. Fourth step is to create a partition scheme<\/a><\/strong><\/li>
  5. Last step: update the existing table to use the partitions<\/a><\/strong><\/li><\/ol>\n\n\n\n
    1. First step is to choose the partitioning column<\/h3>\n\n\n\n
    First, let’s choose the best column for the partition. As the article exposes earlier in the introduction, many queries use the year as a filter to display yearly figures and year over year comparisons.
    The choice of a partition on the year column is an interesting one. The partitioning column is the Year column and it’s an integer.<\/p>\n\n\n\n
    The different partitions on the year will split physically the data in the disk on multiple file groups. This operation is also called partitioning an existing table. Check more details in this article on the definition of a SQL Server partition.<\/p>\n\n\n\n
    Moreover, the table remains one unique logical SQL Server object, but physically it’s split in different files to isolate and improve the access to every partition.<\/p>\n\n\n\n
    2. Second step is to create a partition function<\/h3>\n\n\n\n
    Secondly, to create a partition function, simply use a CREATE statement in order to create the partition function. The parameters are the data type of the partitioning column, the type of partition than can be right or left, and the range of values.<\/p>\n\n\n\n
    For instance, this T-SQL code example shows how to create a partition function<\/a> based on the previous years, the last year, the current year and the next years.<\/p>\n\n\n\n
    CREATE PARTITION FUNCTION ufn_Partition_Sales (int)\nAS RANGE RIGHT\nFOR VALUES (\n\t2018,\n\t2019,\n\t2020\n);<\/pre>\n\n\n
    \n
    \"T-SQL
    T-SQL script to create a partition function with range right option as partition scheme<\/figcaption><\/figure><\/div>\n\n\n
    3. Third step is to create the necessary file groups used by the SQL partitions<\/h3>\n\n\n\n
    Moreover, a partition scheme uses a partition function, and a partition scheme uses file groups. To create the partition scheme, create first the file groups to be used. <\/p>\n\n\n\n
    Above all, make sure to adjust the code before running it. Use the code below to create four additional file groups to the existing Expert-Only database. After that, the next example of code creates the file groups in the file system. In addition, check out the official documentation on partitioned tables and indexes<\/a>.<\/p>\n\n\n\n
    USE [Expert-Only];  \nGO  \n\nALTER DATABASE [Expert-Only] ADD FILEGROUP Sales1filegroup;  \nGO  \n\nALTER DATABASE [Expert-Only] ADD FILEGROUP Sales2filegroup;  \nGO  \n\nALTER DATABASE [Expert-Only] ADD FILEGROUP Sales3filegroup;  \nGO  \n\nALTER DATABASE [Expert-Only] ADD FILEGROUP Sales4filegroup;  \nGO\n<\/pre>\n\n\n
    \n
    \"Alter
    Alter database add a logical file group for the table partition<\/figcaption><\/figure><\/div>\n\n\n
    In addition, the next step is mandatory and assigns the physical files to existing file groups. To do this, let’s add a secondary data file to every file group created.<\/p>\n\n\n\n
    ALTER DATABASE [Expert-Only] ADD FILE   \n(  \n    NAME = Sales1datafile,  \n    FILENAME = \n\t'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\Sales1df.ndf',\n    SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 10MB  \n)  \nTO FILEGROUP Sales1filegroup;\nGO\n\nALTER DATABASE [Expert-Only] ADD FILE   \n(  \n    NAME = Sales2datafile,  \n    FILENAME = \n\t'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\Sales2df.ndf',\n    SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 10MB  \n)  \nTO FILEGROUP Sales2filegroup;\nGO \n\nALTER DATABASE [Expert-Only] ADD FILE   \n(  \n    NAME = Sales3datafile,  \n    FILENAME = \n\t'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\Sales3df.ndf',\n    SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 10MB  \n)  \nTO FILEGROUP Sales3filegroup;\nGO  \n\nALTER DATABASE [Expert-Only] ADD FILE   \n(  \n    NAME = Sales4datafile,  \n    FILENAME = \n\t'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\DATA\\Sales4df.ndf',\n    SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 10MB  \n)  \nTO FILEGROUP Sales4filegroup;\nGO\n<\/pre>\n\n\n
    \n
    \"Alter
    Alter database to add four new NDF secondary datafiles to store the four table partitions<\/figcaption><\/figure><\/div>\n\n\n
    4. Fourth step is to create a SQL Server partition scheme<\/h3>\n\n\n\n
    Further, the partition scheme object defines how to spread the data in the different file groups. Hence improving performance and maintenance of the table.<\/p>\n\n\n\n
    CREATE PARTITION SCHEME sche_Partition_Sales\nAS PARTITION ufn_Partition_Sales\nTO (\n\tSales1filegroup,\n\tSales2filegroup,\n\tSales3filegroup,\n\tSales4filegroup\n);<\/pre>\n\n\n
    \n
    \"Add
    Add a SQL Server partition scheme and use the four data file groups for the sales table<\/figcaption><\/figure><\/div>\n\n\n
    5. Update the existing table or create a new one to use the partition scheme<\/h3>\n\n\n\n
    Finally, the table is partitioned after using the partition scheme and the partition function. The code creates the partitions for the newly created Sales table. It also passes the year as the partitioning column.<\/p>\n\n\n\n
    CREATE TABLE [dbo].[SALES_Partitioned]\n(\n\t[Year] INT,\n\t[MonthName] NVARCHAR(50), \n\t[MonthCurrent] BIT, \n\t[NumberMonth] TINYINT, \n\t[EmployeeNumber] SMALLINT, \n\t[NumberOfClients] INTEGER, \n\t[NumberOfSales] BIGINT, \n\t[Amount_ET] NUMERIC(15,5), \n\t[Amount_IT] DECIMAL(15,5)\n)\nON sche_Partition_Sales (2026);\nGO\n<\/pre>\n\n\n
    \n
    \"Script
    Script in T-SQL to create a table with partitions by year<\/figcaption><\/figure><\/div>\n\n\n
    Insert data in the newly created table partition<\/h2>\n\n\n\n
    To insert data in the newly created table using partitions, is it is simple. The storage is managed by the partition function and a simple Insert statement works as usual.<\/p>\n\n\n\n
    The goal is to insert data for multiple years and check in which partition the data is stored.<\/p>\n\n\n\n
    INSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2017, 'January', 10000);\nINSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2018, 'January', 11000);\nINSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2019, 'January', 12000);\nINSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2020, 'January', 13000);\nINSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2021, 'January', 14000);\nINSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2022, 'January', 15000);\nINSERT INTO [dbo].[SALES_Partitioned] ([Year], [MonthName], [Amount_ET]) values (2023, 'January', 16000);\n<\/pre>\n\n\n\n
    After inserting some values for multiples years, from 2017 to 2023, let’s check in which partition the data is stored. In fact, we can list in which filegroup the Sales data is stored, and how many lines are present. <\/p>\n\n\n\n
    To achieve this, we use mainly two system views: the partitions system view and the dm_db_partition_stats that displays statistics on partitions. <\/p>\n\n\n\n
    select \n\tobject_name(ps.object_id) as TableName,\n\tps.partition_number as PartitionNumber, \n\tfg.name AS FileGroupName,\n\trow_count as [RowCount]\nfrom\tsys.dm_db_partition_stats ps, \n\t\tsys.partitions p\n    join sys.allocation_units au  \n\t\tON au.container_id = p.hobt_id  \n    join sys.filegroups fg  \n\t\tON fg.data_space_id = au.data_space_id \nwhere\tp.partition_id = ps.partition_id\nand\t\tps.[object_id] in (\n\tselect object_id('[dbo].[SALES]') \n\tunion all\n\tselect object_id('[dbo].[SALES_Partitioned]')\n)\norder by 2;<\/pre>\n\n\n
    \n
    \"SQL
    SQL Server query to display table and file group names with number of rows<\/figcaption><\/figure><\/div>\n\n\n
    In the example, the data is stored this way by the database system, because we have selected the RANGE RIGHT option in the Partition Function : <\/p>\n\n\n\n
    1. Sales1filegroup stores the data with year strictly inferior to 2020 (etc., 2017,2018,2019)<\/li>
    2. Sales2filegroup stores the data equal or larger than 2020 and inferior to 2021 (2020)<\/li>
    3. Sales3filegroup contains the data equal or larger than 2021 and inferior to 2022 (2021)<\/li>
    4. Sales4filegroup contains all the data superior or equal to 2022 (2022, 2023, etc\u2026)<\/li><\/ol>\n\n\n\n
      To conclude, this article explains how to create a SQL Server partition table with a partitioning column, a partition function, and a partition scheme.<\/p>\n\n\n\n
      What is a SQL Server table partition?<\/strong> 
      A partition table is a MS SQL table with a partitioning column, it uses a partition function and a partition scheme. In addition, it contains data that is split in the disk to access directly to one single piece of data instead of accessing the full rows of a unique table.<\/p> <\/div> 
      How to create a table partition by year?<\/strong> 
      A table partition by year uses the year as a partitioning column and stores the data for a given year in a specific partition. In other words, the general goal is to have the most frequently used years, like the last year, the current year, and the next year in dedicated separate partitions.  <\/p> <\/div> 
      How to improve SQL Server query performance?<\/strong> 
      To improve the performance of MSSQL queries, creating a table partition and adding indexes help a lot. Moreover, the partitioning helps to physically split the data into different pieces making the system respond faster.<\/p> <\/div> 
      What are the disadvantages of SQL Server partitions?<\/strong> 
      The main disadvantages of MS SQL table partitions are the object maintenance to keep the partition definitions up to date with the partitioning column.<\/p> <\/div> <\/div>\n\n\n\n
      To go further, let’s check out how to create a view with MSSQL to group data from multiple tables<\/a><\/strong>.<\/p>\n\n\n\n
      \nhttps:\/\/expert-only.com\/en\/t-sql\/create-partitioned-view-sql-server-group-tables\/\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"
      How to create a table partition in SQL Server? Let’s consider a large table with Sales data. So, this table have a column that stores the year of the sale and the table stores millions of lines. Let’s consider …<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":6206,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[454],"tags":[],"class_list":{"0":"post-9274","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-t-sql"},"yoast_head":"\nCreate a SQL Server partition - T-SQL<\/title>\n<meta name=\"description\" content=\"To create a SQL Server partition, use this code example to add a partition function and scheme and define a partitioning column on a table.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Create a SQL Server partition\" \/>\n<meta property=\"og:description\" content=\"To create a SQL Server partition, use this code example to add a partition function and scheme and define a partitioning column on a table.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-08-02T05:06:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-02T14:48:11+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/texture-design-BE228CEA431_1920x1080.jpeg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"9 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"Create a SQL Server partition\",\"datePublished\":\"2022-08-02T05:06:00+00:00\",\"dateModified\":\"2022-08-02T14:48:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/\"},\"wordCount\":1103,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/texture-design-BE228CEA431_1920x1080.jpeg\",\"articleSection\":[\"T-SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#respond\"]}]},{\"@type\":[\"WebPage\",\"FAQPage\"],\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/\",\"url\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/\",\"name\":\"Create a SQL Server partition - T-SQL\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/texture-design-BE228CEA431_1920x1080.jpeg\",\"datePublished\":\"2022-08-02T05:06:00+00:00\",\"dateModified\":\"2022-08-02T14:48:11+00:00\",\"description\":\"To create a SQL Server partition, use this code example to add a partition function and scheme and define a partitioning column on a table.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#breadcrumb\"},\"mainEntity\":[{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598466171130\"},{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598466584788\"},{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598470799499\"},{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598470984310\"}],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/texture-design-BE228CEA431_1920x1080.jpeg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/texture-design-BE228CEA431_1920x1080.jpeg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Create a SQL Server partition\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}},{\"@type\":\"Question\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598466171130\",\"position\":1,\"url\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598466171130\",\"name\":\"What is a SQL Server table partition?\",\"answerCount\":1,\"acceptedAnswer\":{\"@type\":\"Answer\",\"text\":\"A partition table is a MS SQL table with a partitioning column, it uses a partition function and a partition scheme. In addition, it contains data that is split in the disk to access directly to one single piece of data instead of accessing the full rows of a unique table.\",\"inLanguage\":\"en-US\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Question\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598466584788\",\"position\":2,\"url\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598466584788\",\"name\":\"How to create a table partition by year?\",\"answerCount\":1,\"acceptedAnswer\":{\"@type\":\"Answer\",\"text\":\"A table partition by year uses the year as a partitioning column and stores the data for a given year in a specific partition. In other words, the general goal is to have the most frequently used years, like the last year, the current year, and the next year in dedicated separate partitions.  \",\"inLanguage\":\"en-US\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Question\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598470799499\",\"position\":3,\"url\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598470799499\",\"name\":\"How to improve SQL Server query performance?\",\"answerCount\":1,\"acceptedAnswer\":{\"@type\":\"Answer\",\"text\":\"To improve the performance of MSSQL queries, creating a table partition and adding indexes help a lot. Moreover, the partitioning helps to physically split the data into different pieces making the system respond faster.\",\"inLanguage\":\"en-US\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Question\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598470984310\",\"position\":4,\"url\":\"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598470984310\",\"name\":\"What are the disadvantages of SQL Server partitions?\",\"answerCount\":1,\"acceptedAnswer\":{\"@type\":\"Answer\",\"text\":\"The main disadvantages of MS SQL table partitions are the object maintenance to keep the partition definitions up to date with the partitioning column.\",\"inLanguage\":\"en-US\"},\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Create a SQL Server partition - T-SQL","description":"To create a SQL Server partition, use this code example to add a partition function and scheme and define a partitioning column on a table.","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:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/","og_locale":"en_US","og_type":"article","og_title":"Create a SQL Server partition","og_description":"To create a SQL Server partition, use this code example to add a partition function and scheme and define a partitioning column on a table.","og_url":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2022-08-02T05:06:00+00:00","article_modified_time":"2022-08-02T14:48:11+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/texture-design-BE228CEA431_1920x1080.jpeg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"Create a SQL Server partition","datePublished":"2022-08-02T05:06:00+00:00","dateModified":"2022-08-02T14:48:11+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/"},"wordCount":1103,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/texture-design-BE228CEA431_1920x1080.jpeg","articleSection":["T-SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#respond"]}]},{"@type":["WebPage","FAQPage"],"@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/","url":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/","name":"Create a SQL Server partition - T-SQL","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/texture-design-BE228CEA431_1920x1080.jpeg","datePublished":"2022-08-02T05:06:00+00:00","dateModified":"2022-08-02T14:48:11+00:00","description":"To create a SQL Server partition, use this code example to add a partition function and scheme and define a partitioning column on a table.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#breadcrumb"},"mainEntity":[{"@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598466171130"},{"@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598466584788"},{"@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598470799499"},{"@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598470984310"}],"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/texture-design-BE228CEA431_1920x1080.jpeg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/texture-design-BE228CEA431_1920x1080.jpeg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"Create a SQL Server partition"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}},{"@type":"Question","@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598466171130","position":1,"url":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598466171130","name":"What is a SQL Server table partition?","answerCount":1,"acceptedAnswer":{"@type":"Answer","text":"A partition table is a MS SQL table with a partitioning column, it uses a partition function and a partition scheme. In addition, it contains data that is split in the disk to access directly to one single piece of data instead of accessing the full rows of a unique table.","inLanguage":"en-US"},"inLanguage":"en-US"},{"@type":"Question","@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598466584788","position":2,"url":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598466584788","name":"How to create a table partition by year?","answerCount":1,"acceptedAnswer":{"@type":"Answer","text":"A table partition by year uses the year as a partitioning column and stores the data for a given year in a specific partition. In other words, the general goal is to have the most frequently used years, like the last year, the current year, and the next year in dedicated separate partitions.  ","inLanguage":"en-US"},"inLanguage":"en-US"},{"@type":"Question","@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598470799499","position":3,"url":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598470799499","name":"How to improve SQL Server query performance?","answerCount":1,"acceptedAnswer":{"@type":"Answer","text":"To improve the performance of MSSQL queries, creating a table partition and adding indexes help a lot. Moreover, the partitioning helps to physically split the data into different pieces making the system respond faster.","inLanguage":"en-US"},"inLanguage":"en-US"},{"@type":"Question","@id":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598470984310","position":4,"url":"https:\/\/expert-only.com\/en\/t-sql\/create-sql-server-partition\/#faq-question-1598470984310","name":"What are the disadvantages of SQL Server partitions?","answerCount":1,"acceptedAnswer":{"@type":"Answer","text":"The main disadvantages of MS SQL table partitions are the object maintenance to keep the partition definitions up to date with the partitioning column.","inLanguage":"en-US"},"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/9274","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=9274"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/9274\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/6206"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=9274"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=9274"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=9274"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}