{"id":10298,"date":"2020-10-16T09:14:47","date_gmt":"2020-10-15T23:14:47","guid":{"rendered":"https:\/\/database.guide\/?p=10298"},"modified":"2020-10-20T10:28:58","modified_gmt":"2020-10-20T00:28:58","slug":"create-a-partitioned-table-in-sql-server-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/create-a-partitioned-table-in-sql-server-t-sql\/","title":{"rendered":"Create a Partitioned Table in SQL Server (T-SQL)"},"content":{"rendered":"\n

SQL Server<\/a> supports partitioned tables and indexes. When a partitioned table or index is partitioned, its data is divided into units that can be spread across more than one filegroup.<\/p>\n\n\n\n

Therefore, to create a partitioned table in SQL Server, you first need to create the filegroup\/s that will hold each partition. You also need to create a partition function and a partition scheme. <\/p>\n\n\n\n

So it goes like this:<\/p>\n\n\n\n

  1. Create filegroup\/s<\/li>
  2. Create a partition function<\/li>
  3. Create a partition scheme<\/li>
  4. Create the partitioned table<\/li><\/ol>\n\n\n\n

    Below is an example of using these steps to create a table with four partitions.<\/p>\n\n\n\n\n\n\n\n

    Create Filegroups<\/h2>\n\n\n\n

    First, we add four filegroups to the database called Test<\/strong>, and then specify the physical file for each of those filegroups.<\/p>\n\n\n\n

    ALTER DATABASE Test\nADD FILEGROUP MoviesFg1;\nGO  \nALTER DATABASE Test  \nADD FILEGROUP MoviesFg2;  \nGO  \nALTER DATABASE Test  \nADD FILEGROUP MoviesFg3;  \nGO  \nALTER DATABASE Test  \nADD FILEGROUP MoviesFg4;   \n\nALTER DATABASE Test   \nADD FILE   \n(  \n    NAME = MoviesFg1dat,  \n    FILENAME = '\/var\/opt\/mssql\/data\/MoviesFg1dat.ndf',  \n    SIZE = 5MB,  \n    MAXSIZE = 100MB,  \n    FILEGROWTH = 5MB  \n)  \nTO FILEGROUP MoviesFg1;  \nALTER DATABASE Test   \nADD FILE   \n(  \n    NAME = MoviesFg2dat,  \n    FILENAME = '\/var\/opt\/mssql\/data\/MoviesFg2dat.ndf',  \n    SIZE = 5MB,  \n    MAXSIZE = 100MB,  \n    FILEGROWTH = 5MB  \n)  \nTO FILEGROUP MoviesFg2;  \nGO  \nALTER DATABASE Test   \nADD FILE   \n(  \n    NAME = MoviesFg3dat,  \n    FILENAME = '\/var\/opt\/mssql\/data\/MoviesFg3dat.ndf',  \n    SIZE = 5MB,  \n    MAXSIZE = 100MB,  \n    FILEGROWTH = 5MB  \n)  \nTO FILEGROUP MoviesFg3;  \nGO  \nALTER DATABASE Test   \nADD FILE   \n(  \n    NAME = MoviesFg4dat,  \n    FILENAME = '\/var\/opt\/mssql\/data\/MoviesFg4dat.ndf',  \n    SIZE = 5MB,  \n    MAXSIZE = 100MB,  \n    FILEGROWTH = 5MB  \n)  \nTO FILEGROUP MoviesFg4;  \nGO  <\/code><\/pre>\n\n\n\n
    You’ll need to change this code, depending on your requirements. You’ll also need to change the file paths to suit your environment. For example, if you’re on Windows, your path might look more like D:\\mssql\\data\\MoviesFg4dat.ndf<\/code>. <\/p>\n\n\n\n
    Also, if you need more partitions add more filegroups here. Conversely, if you need less partitions, specify less filegroups here.<\/p>\n\n\n\n
    Create a Partition Function<\/h2>\n\n\n\n
    Next we create a partition function called MoviesPartitionFunction<\/strong> that will partition the table into four partitions.<\/p>\n\n\n\n
    CREATE PARTITION FUNCTION MoviesPartitionFunction (int)  \n    AS RANGE LEFT FOR VALUES (1, 100, 1000);\nGO<\/code><\/pre>\n\n\n\n
    The int<\/strong> part specifies the data type of the column used for partitioning.<\/p>\n\n\n\n
    All data types are valid for use as partitioning columns, except text<\/strong>, ntext<\/strong>, image<\/strong>, xml<\/strong>, timestamp<\/strong>, varchar(max)<\/strong>, nvarchar(max)<\/strong>, varbinary(max)<\/strong>, alias data types, or CLR user-defined data types.<\/p>\n\n\n\n
    Here, I use three boundary values (1, 100<\/code>, and 1000<\/code>) to specify four partitions. These boundary values must either match or be implicitly convertible to the data type specified in parentheses after the partition function’s name.<\/p>\n\n\n\n
    Given these boundary values, and the fact that I specified a RANGE LEFT<\/code> partition, the four partitions will hold values as specified in the following table.<\/p>\n\n\n\n
    Partition<\/th>Values<\/th><\/tr><\/thead>
    1<\/td><=\u00a01<\/code><\/td><\/tr>
    2<\/td>>\u00a01<\/code>\u00a0AND\u00a0<=\u00a0100<\/code><\/td><\/tr>
    3<\/td>>\u00a0100<\/code>\u00a0AND\u00a0<=1000<\/code><\/td><\/tr>
    4<\/td>>\u00a01000<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    If I had specified a RANGE RIGHT<\/code> partition, the breakdown would be slightly different, as outlined in the following table.<\/p>\n\n\n\n
    Partition<\/th>Values<\/th><\/tr><\/thead>
    1<\/td><\u00a01<\/code><\/strong><\/td><\/tr>
    2<\/td>>=\u00a01<\/code>\u00a0<\/strong>AND\u00a0<\u00a0100<\/code><\/strong><\/td><\/tr>
    3<\/td>>=\u00a0100<\/code>\u00a0<\/strong>AND\u00a0<\u00a01000<\/code><\/strong><\/td><\/tr>
    4<\/td>>=\u00a01000<\/code><\/strong><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    The same concept applies if the partitioning column uses other data types, such as date\/time values.<\/p>\n\n\n\n
    Create a Partition Scheme<\/h2>\n\n\n\n
    Next we need to create a partition scheme.<\/p>\n\n\n\n
    A partition scheme maps the partitions of a partitioned table or index to the new filegroups.<\/p>\n\n\n\n
    In our case, the code will look like this:<\/p>\n\n\n\n
    CREATE PARTITION SCHEME MoviesPartitionScheme  \n    AS PARTITION MoviesPartitionFunction  \n    TO (MoviesFg1, MoviesFg2, MoviesFg3, MoviesFg4);  \nGO<\/code><\/pre>\n\n\n\n
    Notice that we reference the partition function that we created in the previous step. We also reference the filegroups that we created in the first step.<\/p>\n\n\n\n
    Create the Partitioned Table<\/h2>\n\n\n\n
    Finally we can create the partitioned table.<\/p>\n\n\n\n
    CREATE TABLE Movies (\n    MovieId int IDENTITY PRIMARY KEY, \n    MovieName varchar(60)\n    )  \n    ON MoviesPartitionScheme (MovieId);  \nGO<\/code><\/pre>\n\n\n\n
    The only difference between this and creating an unpartitioned table is that when creating a partitioned table, we use the ON<\/code> argument to specify a partition scheme to use. In our case, we specify the partition scheme we created in the previous step, and specify the MovieId<\/strong> column as the partitioning column.<\/p>\n\n\n\n
    You’ll notice that the MovieId<\/strong> column has a data type of int<\/strong>, which matches the boundary values that we specified when creating the partition function.<\/p>\n\n\n\n
    Note that if you use a computed column<\/a> in a partition function, it must be explicitly marked PERSISTED<\/code>.<\/p>\n\n\n\n
    Check the Partition Function<\/h2>\n\n\n\n
    You can use the sys.partition_functions<\/code> view to return all partition functions.<\/p>\n\n\n\n
    SELECT * FROM sys.partition_functions;<\/code><\/pre>\n\n\n\n
    Result (using vertical output):<\/p>\n\n\n\n
    name                    | MoviesPartitionFunction\nfunction_id             | 65536\ntype                    | R \ntype_desc               | RANGE\nfanout                  | 4\nboundary_value_on_right | 0\nis_system               | 0\ncreate_date             | 2020-10-10 05:37:41.330\nmodify_date             | 2020-10-10 05:37:41.330<\/pre>\n\n\n\n
    Check the Partition Scheme<\/h2>\n\n\n\n
    You can use sys.partition_schemes<\/code> to check the partition scheme.<\/p>\n\n\n\n
    SELECT * FROM sys.partition_schemes;<\/code><\/pre>\n\n\n\n
    Result (using vertical output):<\/p>\n\n\n\n
    name          | MoviesPartitionScheme\ndata_space_id | 65601\ntype          | PS\ntype_desc     | PARTITION_SCHEME\nis_default    | 0\nis_system     | 0\nfunction_id   | 65536<\/pre>\n\n\n\n
    Alternatively, you could use the following query to return other details, such as the schema, table, index, etc.<\/p>\n\n\n\n
    SELECT \n    object_schema_name(i.object_id) AS [Schema],\n    object_name(i.object_id) AS [Object],\n    i.name AS [Index],\n    s.name AS [Partition Scheme]\n    FROM sys.indexes i\n    INNER JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id;<\/code><\/pre>\n\n\n\n
    Result (using vertical output):<\/p>\n\n\n\n
    Schema           | dbo\nObject           | Movies\nIndex            | PK__Movies__4BD2941A0ED85ACA\nPartition Scheme | MoviesPartitionScheme<\/pre>\n\n\n\n
    Check the Partitioned Table<\/h2>\n\n\n\n
    You can run the sys.dm_db_partition_stats<\/code> view to return page and row-count information for every partition in the current database.<\/p>\n\n\n\n
    But running that before inserting any data into the table will result in most statistics being zero.<\/p>\n\n\n\n
    So I’m going to insert data first.<\/p>\n\n\n\n
    INSERT INTO Movies\nSELECT name FROM OtherDb.dbo.Movies;<\/code><\/pre>\n\n\n\n
    Result:<\/p>\n\n\n\n
    (4079 rows affected)<\/pre>\n\n\n\n
    We can see that 4,079 rows were inserted.<\/p>\n\n\n\n
    Now let’s query the sys.dm_db_partition_stats<\/code> view.<\/p>\n\n\n\n
    SELECT *\nFROM sys.dm_db_partition_stats\nWHERE object_id = OBJECT_ID('dbo.Movies');<\/code><\/pre>\n\n\n\n
    Result:<\/p>\n\n\n\n
    +-------------------+-------------+------------+--------------------+--------------------------+--------------------------+------------------------------+-----------------------+---------------------------+--------------------------------+------------------------------------+-------------------+-----------------------+-------------+\n| partition_id      | object_id   | index_id   | partition_number   | in_row_data_page_count   | in_row_used_page_count   | in_row_reserved_page_count   | lob_used_page_count   | lob_reserved_page_count   | row_overflow_used_page_count   | row_overflow_reserved_page_count   | used_page_count   | reserved_page_count   | row_count   |\n|-------------------+-------------+------------+--------------------+--------------------------+--------------------------+------------------------------+-----------------------+---------------------------+--------------------------------+------------------------------------+-------------------+-----------------------+-------------|\n| 72057594048413696 | 2030630277  | 1          | 1                  | 1                        | 2                        | 9                            | 0                     | 0                         | 0                              | 0                                  | 2                 | 9                     | 1           |\n| 72057594048479232 | 2030630277  | 1          | 2                  | 1                        | 2                        | 9                            | 0                     | 0                         | 0                              | 0                                  | 2                 | 9                     | 99          |\n| 72057594048544768 | 2030630277  | 1          | 3                  | 3                        | 5                        | 25                           | 0                     | 0                         | 0                              | 0                                  | 5                 | 25                    | 900         |\n| 72057594048610304 | 2030630277  | 1          | 4                  | 10                       | 12                       | 33                           | 0                     | 0                         | 0                              | 0                                  | 12                | 33                    | 3079        |\n+-------------------+-------------+------------+--------------------+--------------------------+--------------------------+------------------------------+-----------------------+---------------------------+--------------------------------+------------------------------------+-------------------+-----------------------+-------------+<\/pre>\n\n\n\n
    This view returns a lot of columns, so let’s narrow the columns down to just a couple.<\/p>\n\n\n\n
    SELECT \n    partition_number,\n    row_count\nFROM sys.dm_db_partition_stats\nWHERE object_id = OBJECT_ID('dbo.Movies');<\/code><\/pre>\n\n\n\n
    Result:<\/p>\n\n\n\n
    +--------------------+-------------+\n| partition_number   | row_count   |\n|--------------------+-------------|\n| 1                  | 1           |\n| 2                  | 99          |\n| 3                  | 900         |\n| 4                  | 3079        |\n+--------------------+-------------+<\/pre>\n\n\n\n
    We can see how the rows are allocated across the partitions. They’re allocated exactly as we specified in the partition function. The rows total 4,079, which is exactly how many rows we inserted.<\/p>\n\n\n\n
    However, it’s worth noting that the Microsoft documentation actually states<\/a> that this column is just an approximate<\/em> count of the rows in each partition.<\/p>\n\n\n\n
    Best Practice<\/h2>\n\n\n\n
    Microsoft recommends<\/a> that we always keep empty partitions at both ends of the partition range. <\/p>\n\n\n\n
    This is in case you need to either split or merge the partitions in the future.<\/p>\n\n\n\n
    The reason for this recommendation is to guarantee that the partition split and the partition merge don’t incur any unexpected data movement.<\/p>\n\n\n\n
    Therefore, given the data in my example, I could change the partition function to look something like this:<\/p>\n\n\n\n
    CREATE PARTITION FUNCTION MoviesPartitionFunction (int)  \n    AS RANGE LEFT FOR VALUES (-1, 100, 10000);\nGO<\/code><\/pre>\n\n\n\n
    Or if I anticipate more than 10,000 rows, I could use a larger number (or create more partitions).<\/p>\n\n\n\n
    If I were to recreate all the steps again, to create my partitioned table, my partition stats would look like this:<\/p>\n\n\n\n
    SELECT \n    partition_number,\n    row_count\nFROM sys.dm_db_partition_stats\nWHERE object_id = OBJECT_ID('dbo.Movies');<\/code><\/pre>\n\n\n\n
    Result:<\/p>\n\n\n\n
    +--------------------+-------------+\n| partition_number   | row_count   |\n|--------------------+-------------|\n| 1                  | 0           |\n| 2                  | 100         |\n| 3                  | 3979        |\n| 4                  | 0           |\n+--------------------+-------------+<\/pre>\n\n\n\n
    Now my data is concentrated into the middle two partitions, and the partitions at both ends are empty.<\/p>\n","protected":false},"excerpt":{"rendered":"
    SQL Server supports partitioned tables and indexes. When a partitioned table or index is partitioned, its data is divided into units that can be spread across more than one filegroup. Therefore, to create a partitioned table in SQL Server, you first need to create the filegroup\/s that will hold each partition. You also need to … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[10,77,105,61,26],"class_list":["post-10298","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-how-to","tag-mssql","tag-partitioning","tag-t-sql","tag-tables"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/10298","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=10298"}],"version-history":[{"count":14,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/10298\/revisions"}],"predecessor-version":[{"id":10390,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/10298\/revisions\/10390"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=10298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=10298"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=10298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}