SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table

I have received call from my DBA friend who read my article SQL SERVER – 2005 – Introduction to Partitioning. He suggested that I should write simple tutorial about how to horizontal partition database table. Here is simple tutorial which explains how a table can be partitioned. Please read my article Introduction to Partitioning before continuing to this article.

Step 1 : Create New Test Database with two different filegroups
I have written tutorial using my C: Drive, however to take advantage of partition it is recommended that different file groups are created on separate hard disk to get maximum performance advantage of partitioning. Before running following script, make sure C: drive contains two folders – Primary and Secondary as following example has used those two folder to store different filegroups.
USE Master;
--- Step 1 : Create New Test Database with two different filegroups.
FROM sys.databases
WHERE name = N'TestDB')
(NAME = 'TestDB_Part2',
SIZE = 2,

partition1 SQL SERVER   2005   Database Table Partitioning Tutorial   How to Horizontal Partition Database Table
Step 2 : Create Partition Range Function
Partition Function defines the range of values to be stored on different partition. For our example let us assume that first 10 records are stored in one filegroup and rest are stored in different filegroup. Following function will create partition function with range specified.
--- Step 2 : Create Partition Range Function

partition2 SQL SERVER   2005   Database Table Partitioning Tutorial   How to Horizontal Partition Database Table Click on image to see larger image
Step 3 : Attach Partition Scheme to FileGroups
Partition function has to be attached with filegroups to be used in table partitioning. In following example partition is created on primary and secondary filegroup.
--- Step 3 : Attach Partition Scheme to FileGroups
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2);

Step 4 : Create Table with Partition Key and Partition Scheme
The table which is to be partitioned has to be created specifying column name to be used with partition scheme to partition tables in different filegroups. Following example demonstrates ID column as the Partition Key.
--- Step 4 : Create Table with Partition Key and Partition Scheme
ON TestDB_PartitionScheme (ID);

Step 5 : (Optional/Recommended) Create Index on Partitioned Table
This step is optional but highly recommended. Following example demonstrates the creation of table aligned index. Here index is created using same Partition Scheme and Partition Key as Partitioned Table.
--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table
ON TestTable(ID)
ON TestDB_PartitionScheme (ID);

Step 6 : Insert Data in Partitioned Table
Insert data in the partition table. Here we are inserting total of 3 records. We have decided that in table partition 1 Partition Key ID will contain records from 1 to 10 and partition 2 will contain reset of the records. In following example record with ID equals to 1 will be inserted in partition 1 and rest will be inserted in partition 2.
--- Step 6 : Insert Data in Partitioned Table
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2

Step 7 : Test Data from TestTable
Query TestTable and see the values inserted in TestTable.
--- Step 7 : Test Data from TestTable
FROM TestTable;

Step 8 : Verify Rows Inserted in Partitions
We can query sys.partitions view and verify that TestTable contains two partitions and as per Step 6 one record is inserted in partition 1 and two records are inserted in partition 2.
--- Step 8 : Verify Rows Inserted in Partitions
FROM sys.partitions

partition3 SQL SERVER   2005   Database Table Partitioning Tutorial   How to Horizontal Partition Database Table
Partitioning table is very simple and very efficient when used with different filegroups in different tables. I will write very soon more articles about Table Partitioning. If you need any help in table partitioning or have any doubt, please contact me and I will do my best to help you.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

132 thoughts on “SQL SERVER – 2005 – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table

  1. Pingback: SQL SERVER - 2005 - Introduction to Partitioning Journey to SQL Authority with Pinal Dave

  2. Hi

    Can anyone please help me out,

    Does PARTITION TABLE help to improve performance in terms of INSERTION into a TABLE ?

    I have two windows services, which pick the MESSAGE from MSMQ and inserts the data into a TABLE of SQL Server 2005, execution of any heavy query hampers insertion process of my services and pileup gets started at MSMQ end.

    Would it be really useful to me if i create two partitions into my table for respective windows services ?

    NOTE : Table contains billions of record


  3. Thanks for the article.. got a question.

    With a table with horizontal partition in place (say by id) if the select statement in your store proc does not have a where clause of account id, then that store proc won’t benefit from the partitioning right ? ie. no perf gain?


    • Yes Kenneth… In order to make use of partitions effectively, the query’s where clause should have the column information associated to partitions.

      Since the query wont have the account information , it will have to check all partitions to get the data assuming the partitions are created on Account


  4. Is it possible for an admin to move a partition from one server to another server as part of archiving? From all the reading, it does not seem to be possible.



  5. We have OLTP environment with 50,000 concurrent user and considering table partition option due to CPU bottleneck. We dont have nay memory issue on system but as User grows, CPU increases. Since we are moving data between partitions and we use data using Partition column, wont our CPU on Select statements on table come down and improve CPU? Got confused from article as it says there wont be significant gain if its CPU issue. Please clarify.



  6. We have 2 tables in our DB Schema.
    Table1 is partioned by col1 and table2 is partitioned by col2.

    now we have to insert values to table1 and table2 inside one transaction.

    we are getting error saying
    transaction cannot be used across multiple partitions.

    Any thoughts on using transaction across multiple partitions



  7. Hi Dave,

    I wanted to check if it possible to create a Table Partition on the column which is not a primary key.
    Can you please advise.

    Thanks in Advance.



  8. Hello, I have a table with 100 million rows of values ( id, date ,value) .. 90% of the time only the current month data is accessed. Nightly 50,000 rows are inserted with yesterday’s value. In the past, i would create a history table and archive the older data.. is partioning by date a good solution?


  9. I still need to know How I can partition my existing database. I released my application online before a week, and now I have more than 2 million records in my table. Can you lead me how I can partition my existing huge table.


  10. Hi,

    I have several tables with more than 2 million records. It is about invoices, invoicedetail, invoicedescription (multi-language) and invoicepayments. I get time-outs everywhere in my software due to this huge tables. Any idea if partitioning will solve this? Do you have an idea how I could partition these?



  11. Can you tell me how to convert an existing table into a partition table? I have some tables that need to be partitioned due to the huge amount of data stored..
    Thanks in advance :)


  12. @ Andy
    Dave’s article is excellent, I follow the instructions step by step and works great.
    Rename the your table, follow all the steps described above and at the end just insert the data from the old renamed table into newly created (the one that you just partitioned).


  13. Hi

    I have a very large table containing almost more than 100 million records and when I want to do a search performance is very low, so I decided to use partitioning and create 4 partitions based on a CHAR field in the table.
    Do you think this increases search performance?
    The DB is already created and it has one file group, what can I do about that?

    Could you please let me know?Thanks



  14. Hi
    I created 5 partitions for my table as the following code:

    CREATE PARTITION FUNCTION ParitioningTest_PartitionRange(nchar(4))

    CREATE PARTITION SCHEME ParitioningTest_PartitionScheme
    AS PARTITION ParitioningTest_PartitionRange
    TO ([FG_CARD], [FG_CBO],
    [FG_TELL], [FG]);

    Now I dont know how to move records from ‘Transaction_Summary’ table to this partitions.

    I should mention that all this partitioning is for this “Transaction_Summary” which has more than 100 million records and there in a CHAR(4) field in this table called “Channel” which doesnt have an index(there is an id column which is primary key” and I want to do the partitioning based on this filed, I know if that filed was a primary ke I could say

    Drop index Transaction_Summary_IXC on Transaction_Summary with (Move To [ParitioningTest_PartitionScheme] (Channel) )

    But I dont know what to do in this case, could you please help me?



  15. Hi pinal,

    We are facing some problems in our company.

    we have a very huge dataset ~100M and we have used partitioning for the same.
    All the data was supposed to be residing in the partitions created according to the statecodes but the size of primary mdf file has increased to a bulk.
    Can you suggest a solution or reason for.



  16. Can i create a partition with multiple columns as my partition key. If Yes, can you give an example for me. Iam in a pressing need for that please do reply


  17. Hi Pinal,

    This article is excellant. Thanks for this.
    I have a very huge table and i wish to partition this table based on a date field with out affecting other tables. How can i partition this table which has millions of records? Any impacts on partitioning a table which has data?

    Thanks In Advance,


  18. ‘C:\Data\Primary\TestDB_Part1.mdf’,
    FILEGROUP TestDB_Part2
    (NAME = ‘TestDB_Part2’,

    i have one doubt in the post. Here ‘TestDB_Part2.ndf’ is ‘.mdf’ or ‘.ndf’?


      • Yeah… .NDF – Secondary database file used by Microsoft SQL Server; created when the primary .MDF database becomes too large (such as when there is no disk space left); may also be manually added to the SQL Server File Group to distribute data between the two files.


  19. I have doubt on this, how do partitioning on the existing tables?

    you have partitioned for new table but how do we know this table gets filled in future? I am not clear this point (New table partitioning) can u clear me out this?

    Thanks in advance


  20. Is there a way to automate backup and testing restore process for all partitioned databases on a server. I have databases partioned for each month and i would like to backup only recent 3 years of data and the rest would like to backup only once in FULL. is this possible?


  21. Hi,

    My Database is a Decision Support System.

    I have table that contains 100 Million records, and each day we bulk copy and insert 300,000 more records. The main problem we are facing is as per our logic, the data for the past 25 days is only required when performing other business logic. We do not want to have archiving and moving old data to history tables, so any given day all the data for the last 2 years exist. Now my problem is when creating indexes I dont need the index to be on the previous months rather than I need the index if it can cover the last 1 month of Date range. If I do table partitioning how will be my performance increase or is there any other way round for this problem.


  22. hi, i need help please can any body help,
    i want to retrieve data from one table, between two given dates, if these dates are of different years example 2008 & 2009, then im getting error. im converting with the convert function with the code 101.
    any help in this regard will be help full.


  23. @Milni,

    To Start, I would say…

    Create new datafiles in same or different hard disk to improve IO Performance ( if it is raid 5 or 10 then for DR ).

    Again each datafile can have multiple file groups, you can have similar objects created under one filegroup, like all tables in one filegroup and all Indexes in other file group ( this gives you oppurtunity to take backup of individual filegroup, also improves performance ..


  24. I have crores of records in a table and lakhs or record are added everyday.

    Will partitioning improve performance drastically and if yes how many partitions would be most suitable to have on these many number of records.


  25. The question asked about queries not using the partion key was good I just dont see an answer.
    Do youhave to query by the partition key to get the benefits of the partitions?


  26. Hi Pinal Dave,

    I have a very big database, so it takes me about 1 hours to run one report. Can I use partion to divide my database to many smaller partition by every month? Can we store the data of one month in one partition?


    partition_2009_01: will store all data which insert in January.
    partition_2009_02: will store all data which insert in February.

    Thanks and Regards.


  27. Hi Pinal,

    I have a requirement where a table on its own is 600GB and the data is kept for 30 days. Every weekend there is a job which triggers 40 others bulk insert jobs which pumps data into the database from different locations.
    Since there is no common factor to create the partition I have decided to create it based on day
    Every months has got 1,2,3,4……….30 or 31
    so through of creating 31 file groups and attached 31 file ndf files.

    I am not sure if this can be done as I tried couple of times but the partition cannot pick up values from the getdate() field…

    Any help would be really appropriated!



  28. i have more then 5 rows in one table(i,e table1) i need to copy the rows into other table (i’e table2) which have its own rows in it.
    when i try
    INSERT tbl_Education (
    SELECT Standard,School,
    Board FROM tbl_Prev_Education

    then it is just inserting one row (if tbl_Prev_Education has one row)
    when tbl_Prev_Education has more then one row then it is not inserting in to tbl_Education table

    anyone plz help me


  29. Hi, I have table with 6 months data(11 lacs). On this table we are performing grouping. For that the query taking around 2 sec. This table already has the index. So to improve the performance I am trying to implement the partition based on date field. I want to partition the table into month wise. will it help to improve the performance?


  30. Hello pinal,
    I have been create a partition on existing table. this table having 10 to 12 indexes. so which index I would be drop and recreate.

    pleases replay on mail id.



  31. Hello

    if i create new coulums and new sql query (select, insert, update or delete) after i have do partition, Should i do partition again. Or my partition will see my new coulums and my new sql query. Also i can create new table too.



  32. Hi Pinal,How can I mention 50% records instead of mentioning first 10 records should store in one filegroup and rest should be stored in different filegroup?

    If you dont know exactly how many records will be inserting into partioned table then you cant say first 10 records,then i would like to menion % instead of fix numbers.
    Please can you reply with answere.



  33. Hi,

    I have gone through your tutorial and havinf sme question. I have one table where it’s aroung 500 million records now after reading your article i want o use partition.I have two date columnn in this table one is Recd_Date and another is Entered_Date.Now i want to create partition based on entered date and that should be monthly.I am afraid to do that becauase this table is currently using for production.

    And i do not have any file group in the database so needyour suggestion on this.



  34. hi Pinal,

    I found the data partitioning article interesting and tried to implement for my appliaction. The impletmentation discussed in this article is done.

    My doubt is regarding the deletion part, in the site “http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx “, after creating a staging table the older data is switched and later the stagign table is dropped…
    Can you suggest any alternative mthod available to drop partion with lesaa complexity.

    Thanks in advance


  35. Hi,

    To gain perf. improvement you would need to have an index on the partition key as Pinal Dave statues. The queries would need to be using that column as well.

    Even though switching partition in and out of partition table to staging tables are quite complicated it’ s not too complicated. And it’s REALLY fast! Because it’s just a meta data change.

    So – as one of you asked. I would remove archival data to other server by one of the following alternatives, depending on network capacity among other parameters. 1) switching the old partitions to a staging table.
    2) Copying the staging table data to
    2.1a) another “staging” db on same server
    2.1b) another db on the other archival server
    3) if 2.1b then done. If 2.1a make backup of staging db, move backup file to other server, restore it there to a staging db on that server, move data to archival db between db on hist.server


  36. I have brought a file group offline in SQL Server 2005, Now I don’t know how to bring it back online ? Please help, due to this I am unable to take the backup of that particular file group.


  37. Hello Pinal Dave,

    Nice to read you article. I have created partation after reading your article. But i need some advice or code by which i can create Dynamic Partation. Like if in one table i have 4 month data in a year then i should have 4 partation .After one month if one more month is add in the table then it dynamically add one more partation and also if any previous month data is not aviliable or get deleted then remove that partation from database. Please help me to achive this.


  38. Hello sir,

    Your Article On partitions is excellent. It’s really Helpful me.

    I just want to know sir, I have one Question.

    How can i Partition On Two different On same table

    For Example

    I Want To Create two Partition.
    1)On year Column means (1994 to 1999)
    Inside it another partition
    2) On Car Column means(Toyota,Hyundai,Bmw)

    Pls if possible reply soon

    Thank you Sir


  39. Hi Pinal,

    I have renamed a table which was involved in table partition, After rename, partition function points to new name. Will there be any issues in long run during switchout or drop old partition when a table is renamed.

    I haven’t tested it as the table has more than 100 million records

    Please suggest.


    Suresh kumar


  40. Hello Pinal Dave,
    As per your article what will happned if i create ID as primary key. As i executed

    SELECT *
    FROM sys.partitions

    after making ID as primary key i found that one more partiton increased also number of rows in newely add partition is total count of table .Is it correct ? if not then how do we implement Unique cluster index on primary key with partition. Please advice.


  41. hi,
    i am having table with 1 lakh records, i want to do partition on this table based on the column with name status.

    i want to move all separated and active records to two different partitions.

    please guide me how to do this ?



  42. i m running the code for partition as

    use master
    create database testdb
    on primary
    (name=test_part1, filename=’c:\partitionpractice\test_part1.mdf’,size=4mb,maxsize=100,filegrowth=1),
    filegroup testdb_part2


    use Northwind
    create partition function partfunc1 (int)
    range left
    for values(10)

    use Northwind
    create partition scheme mypartschm
    as partition partfunc
    to ([primary],testdb_part2)

    after runing the partition scheme i get the error As

    Msg 208, Level 16, State 58, Line 2
    Invalid object name ‘testdb_part2’.


  43. i m running the code for partition as

    use master
    create database testdb
    on primary
    (name=test_part1, filename=’c:\partitionpractice\test_part1.mdf’,size=4mb,maxsize=100,filegrowth=1),
    filegroup testdb_part2


    use Northwind
    create partition function partfunc1 (int)
    range left
    for values(10)

    use Northwind
    create partition scheme mypartschm
    as partition partfunc
    to ([primary],testdb_part2)

    after runing the partition scheme i get the error As

    Msg 208, Level 16, State 58, Line 2
    Invalid object name ‘testdb_part2′.

    pls reply to this comment as the above comments mail id is Wrong


    • More imp You created database “TestDB” and you are searching in the “Northwind” for the existence of your filegroup. and Please change the Partition schema definition to use the Partition Function.

      Instead of using partfunc (which is not exists) to partfunc1 (Which you created).


  44. When I partitioned a table, I noticed the below:
    1. The Primary key should include the partition key on a Partitioned Table.
    2. But if I am creating this Primary key on the Primary Filegroup or not on the partition, it is letting me do so without including the partition key. Is this a good practice to have index/ Primary key not on Partition. Please advice.


  45. Hi!
    We have a very large table partitioned by month.
    Current month is partitioned by days, older only by months.
    Partitioning is automated. all works smoothly.
    One month-partition holds about 500 mio. records. Older partitions are read-only.

    We want to get rid of the oldest one. I mean not switch to an empty one, we really and completely want it GONE without a trace.
    do we really need to copy all data into an empty, stand-alone table???
    Is there no faster way to drop the partition/filegroup? Anything?

    Thank you so much for your ideas!


    • Andrea,

      Will you please let me know how you guys implemented the below automation process? As i need to implement the same in my business. Please reply back ASAP.

      “Current month is partitioned by days, older only by months.Partitioning is automated.”

      Thanks in Advance….


  46. hi pinal…

    we are planning the partitioning in our database…but we are not clear about the performance benefits..can u please show how and where can i see those performance benifits..?



  47. Hi

    I wish to get your article on how to partition based on day such as 1,2,3…31 and use the ring structure rotating window and do not required merge and split.

    the data will be kept for 1 month, and i am thinking this structure meets my requirement as I can age the data in each partition which consists of 1 day of data before the next month comes.

    But the problem is the date field in the table is not use in most of the user query but there are other non cluster index (composite indexes) in the exisitng huge table. Will this a tradeoff?


    table 1
    id1 (PK)

    non cluster index is id1, field1,field2

    but i will age the data based on insert_dm
    user will query the data based on non cluster indexes.
    ETL will base on insert_dm too.

    the data is huge and over 20 billions of rows per month, so, do you think, if I partition it based on day of the insert_dm, and use the ring structure, it is the a good option.

    any suggestion and example will be great.


  48. Be aware that under certain conditions partitioning will not be an option due to the following bug in the query optimizer:


    The bug results in certain queries performing a complete index sort, rather than just using the known order of an index to determine the result.
    This is very unfortunate if your table contains billions of rows.

    Examples of simple queries affected by the bug:
    A. “SELECT MAX(ColumnN) FROM PartitionedTable” (with a non-clustered aligned index on ColumnN)
    B. “SELECT TOP 10 ColumnX, ColumnY, ColumnZ FROM PartitionedTable ORDER BY ColumnX, ColumnY, ColumnZ” (with a clustered aligned index on ColumnX, ColumnY, ColumnZ)

    In our case the above queries resulted in a difference in execution duration of milliseconds vs. hours, for the exact same queries, on non-partitioned and partitioned tables respectively. :(

    As far as I know Microsoft has no plans to fix this bug in current releases of SQL Server, and has no plans to fix it in the next major either.

    So for an enterprise solution that allows for you to:
    -prioritize data in the same table on physical storage (because all data has to be available online, but only some is frequently queried)
    -keep the physical prioritization transparent to the layers above the database (because you already have existing applications using the database, and/or is using ORMs in the data access layer)
    -keep your indexes aligned to utilize PARTITION SWITCH (because your maintenance window for moving less relevant data to low priority storage is limited)
    -use TOP N/ORDER BY style queries on a large amount of rows (because who doesn’t :) )

    … you will have to look elsewhere.


  49. Hi ALL,
    using: Sqlserver 2008 enterprise edition

    we have database partitioned basing on date [one partition for each month]. Now i want to shrink all the data files [one file per partition] in partitions except the current month partition, that is i have to shrink all the data files in partitions till dec 2010.
    I am trying to get the partition name ,file group name, file name and the partition range value from below system views , but cannot find the approprtiate column to join

    from sys.partitions.

    My main requirement: to retreive database file names in the file groups till last month.

    Please help me.
    Thanks in advance


  50. Hello Pinal,

    I want to partition my 500 GB database into multiple LUNS, the method would be like group of tables…
    Like: All tables related to PMS should be in one LUN
    All tables related to PMO in another
    All tables related to RND in another so on….
    Currently DB in two files on one filegroup .
    Could you please suggest the steps.

    Thanks in advance


  51. hi,

    i have a large table and have thousands of rows, i want to partition this table but the problem is this table has lot of references with other tables if i re-create this table using partition schema then i lost all references so my question is without affecting anything can i put this table in a partition?


  52. Dear Pina,
    How to revert back from Partioning to Single Level Database.

    Example: DB Name Shop

    c:\data\shop.mdf (200MB)
    d:\data\tran.ndf (100MB) clustered index
    e:\data\hist.ndf (50MB) clustered index

    I have to convert my database to


    I have done every thing..like I removed clustered index and and shrink file but when i go for deletion it says “Can not delete file or filegoup because it is not empty.

    please help me.. in a step by step.


  53. hi, how can you resolve error like this. thanks.

    ALTER TABLE SWITCH statement failed. The specified partition 13 of target table ‘jfcdw_prd.dbo.FactSales_CK’ must be empty


  54. Interesting thread…

    I have a requirement to set-up an MS SQL Server 2005 (or 2008) database to hold significant amounts of transaction data. One table needs to hold 26 Gigabytes of data and the other, 3 Gigabytes. This is for data querying of the transaction data.

    First question is ‘Can it be done?’ in MS SQL Server.

    Would I need to partition the tables over a number of hard drives to make this work?

    What typical hardware configuration/approach would be recommended if the answer is YES?

    Thanks in advance folks for any help, much appreciated…




  55. Thank you for the excellent articles. These have been a life saver several time.

    I have partitioned my table by a DateTime field “RecordTime” and have partitioned it by MONTH

    Two questions:

    1. Is there a query to retrieve the field name on which a table was partitioned ?

    2. Is there a query to retrieve the partition ranges? In my example I am expecting to see the list of current partition ranges like Jan2011, Feb2011




    When I created Partition for my database then :

    Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition of SQL Server supports partitioning.

    How can I solve with this situation? please explain to me

    I am very appreciated !



  57. Here is the complete solution:




    CREATE PROCEDURE [dbo].[sp_dim_date_monthly_partition] @nRunDate datetime = NULL

    SET @nRunDate = ‘2011-05-02’
    — Constants used in this procedure
    DECLARE @cRtnSuccess integer
    SET @cRtnSuccess = 0

    DECLARE @cRtnFailed integer
    SET @cRtnFailed = 1

    — Setting up variables used in this procedure
    DECLARE @ErrCount int
    SET @ErrCount = 0

    — Obtaining the 1st at midnight of given date, if no date is given use the current date
    DECLARE @nDate datetime
    DECLARE @dateid numeric(8,0)
    SET @nDate = DATEADD(mm, DATEDIFF(mm,0,ISNULL(@nRunDate,GetDate())), 0)
    SELECT @dateid = (YEAR(@nDate) * 10000) + (MONTH(@nDate) * 100) + DAY(@nDate)

    — Declare Database pathname variables
    DECLARE @db_name nvarchar(4000)
    DECLARE @path nvarchar(max)

    — Declare Filegoup variables
    DECLARE @fg_name nvarchar(max)
    DECLARE @filegroup_sql nvarchar(max)

    — Declare File variables
    DECLARE @file_name nvarchar(max)
    DECLARE @file_sql nvarchar(max)

    — Declare Partition Variables
    DECLARE @ps_name nvarchar(max)
    DECLARE @pf_name nvarchar(max)
    DECLARE @pf_sql nvarchar(max)
    DECLARE @ps_sql nvarchar(max)

    — Set Variable values
    SET @db_name = ‘Tutorial’
    SET @fg_name = ‘fg_date_monthly’
    SET @file_name = ‘saledate_’ + replace(convert(varchar(7),@nDate,121),’-‘,”)
    SET @ps_name = ‘ps_date_monthly’
    SET @pf_name = ‘pf_date_monthly’

    SELECT @path = [filename] FROM master..sysdatabases WHERE NAME like @db_name
    IF charindex(‘\’, @path) > 0
    SELECT @path = substring(@path, 1, len(@path) – charindex(‘\’, reverse(@path)))
    SET @path = @path


    IF Not Exists (SELECT * FROM sys.filegroups WHERE NAME = @fg_name)
    SET @filegroup_sql =
    ‘ALTER DATABASE ‘ + QuoteName(@db_name,”) + ‘ ‘ + ‘ADD FILEGROUP ‘ + QuoteName(@fg_name,”)
    EXEC( @filegroup_sql )


    IF NOT EXISTS (SELECT * FROM sys.sysfiles WHERE NAME = @file_name)
    SET @file_sql =
    ‘ALTER DATABASE’ + ‘ ‘ + QuoteName(@db_name,”) + ‘ ‘ + ‘
    ADD FILE( Name = ‘ + QuoteName(@file_name,””) + ‘,
    FileName = ‘ + QuoteName( @Path + ‘\’ + @file_name + ‘.ndf’, ”” ) + ‘,
    ) TO FILEGROUP ‘ + @fg_name
    EXEC( @file_sql )


    IF NOT EXISTS(SELECT * FROM sys.partition_functions WHERE NAME = @pf_name)
    SET @pf_sql =
    ‘CREATE PARTITION FUNCTION ‘ + QuoteName(@pf_name,”) + ‘(numeric(8,0)) ‘ +
    ‘AS RANGE RIGHT FOR VALUES (20110301,20110401)’
    EXEC( @pf_sql )


    IF NOT EXISTS(SELECT * FROM sys.partition_schemes WHERE NAME = @ps_name)
    SET @ps_sql =
    ‘CREATE PARTITION SCHEME ‘ + QuoteName(@ps_name,”) + ‘ ‘ +
    ‘AS PARTITION ‘ + QuoteName(@pf_name,”) + ‘ ALL TO (‘ + @fg_name + ‘)’
    EXEC( @ps_sql )


    DECLARE @table_sql nvarchar(max)
    IF NOT EXISTS(SELECT * FROM sys.objects WHERE NAME = ‘tblsaledate’ AND type = ‘U’)
    SET @table_sql =
    ‘CREATE TABLE dbo.tblsaledate
    sale_id int not null identity(1,1),
    sale_date datetime not null,
    sale_date_id numeric(8,0)
    ) ON ‘ + QuoteName(@ps_name,”) + ‘(sale_date_id)’
    EXEC( @table_sql )

    — POPULATE tblsaledate TABLE with data
    DECLARE @startdate datetime
    DECLARE @enddate datetime
    DECLARE @thisdate datetime
    DECLARE @day_id int
    SET @startdate = ‘2011-03-01’
    SET @enddate = ‘2011-06-01’
    SET @thisdate = @startdate
    SET @day_id = 1
    WHILE (@thisdate <= @enddate)
    INSERT INTO dbo.tblsaledate (sale_date, sale_date_id)
    VALUES (@thisdate, CAST(convert(varchar(8),@thisdate,112) As int))
    SET @day_id = @day_id + 1
    SET @thisdate = DATEADD(day, 1, @thisdate)


    DECLARE @aps_sql nvarchar(max)
    SET @aps_sql =
    'ALTER PARTITION SCHEME ' + QuoteName(@ps_name,'') + ' NEXT USED ' + @fg_name
    EXEC( @aps_sql )

    DECLARE @apf_sql nvarchar(max)
    SET @apf_sql =
    'ALTER PARTITION FUNCTION ' + QuoteName(@pf_name,'') + '()
    SPLIT RANGE ' + '(' + CAST(@dateid as nvarchar) + ')'
    EXEC( @apf_sql )


    SET @ErrCount = @@ERROR
    IF (@ErrCount 0)
    RETURN @cRtnFailed
    RETURN @cRtnSuccess


    1. Data and Partition Views

    SELECT * , $partition.pf_date_monthly(sale_date_id) FROM dbo.tblsaledate

    SELECT * FROM sys.partitions WHERE object_id = object_id(‘tblsaledate’)

    2. Usage
    Exec [dbo].[sp_dim_date_monthly_partition]

    DROP PROCEDURE [dbo].[sp_dim_date_monthly_partition]
    DROP PARTITION SCHEME [ps_date_monthly]
    DROP PARTITION FUNCTION [pf_date_monthly]




  58. Hi,

    Thanks for such a nice article, I have a question regarding the partition, that is I thought it will divide the rows between two parts through out the insertion process but it has inserted only 10 rows in first part and all other in to part2, is there a way I can equally distribute the rows among two partitions.

    Thanks & Regards


  59. Dealing with massive table is not easy,recently I had to correct a partition scheme which contains 5*12 months’ partitions .you should be very careful at the time of creating partition scheme and function since a minor mistake might cause a serious problem that correcting it may seems impossible as my case.
    My advice to all is to understand its concept completely.


  60. we’ve a massive SQL Server 2005 DB(Enterprise) comprising nearly 1500 tables which is partitioned. I ported the same database (NV_25_0.mdf) to my PC having (Standard) similar version.

    But when attaching it has thrown an error saying that due to a partitioning this cannt be attached.

    Pls. advise me to remove the partition and attach it to SQL Server in my PC….


    • Once the partitions are created on the tables , the information is stored in the SQL Server metadata tables.
      Now when the select queries are fired , the metadata information is compared with the where clause and appropriate partitions are only scanned for retrieval of data and not all the partitions.

      Think of it like this.. India is made up of 28 states… Relate each of the state as partition… Now if I need information of a person residing in Gujarat , the query will hit only the people information staying in gujarat as opposed to all 28 states and thereby improving performance of the query


  61. Very nice tutorial about creating Partition in SQL Server. Could you please explain how to check the existing partition’s various details like on which column the partition has been created,etc on a database and how to remove the partition that has been created.


  62. hi pinal,

    partition column and clustered index key column is different. that time i created partioned for table. but table–>properties–>storage doesn’t show partition. but if i use select * from sys.partitions where object_name(object_id)=’tablename’. it show partioning except clustered index..what is the difference?

    Thanks in advance for you help


  63. hi pinal,

    partition column and UNIQUE clustered index key column is different. that time i created partioned for table. but table–>properties–>storage doesn’t show partition. but if i use select * from sys.partitions where object_name(object_id)=’tablename’. it shows partioning except clustered index..what is the difference?..is this correct way to partition?

    advance THANKS for you help


  64. IF EXISTS (
    SELECT name
    FROM sys.databases
    WHERE name = N’TestDB’)
    for what purpose we are using it
    kindly tell me


    • I got the answer of my question,

      Select $partition.TESTDB_PARTITIONRANGE(id) AS [Partition Number],* from testtable
      where $partition.TESTDB_PARTITIONRANGE(id)=1

      if some one is having any other or better way, please share.



  65. Pingback: SQL SERVER – Advantages of Partitioning – Quiz – Puzzle – 30 of 31 « SQL Server Journey with SQL Authority

  66. I am new to using SQL server but I know with your help I will understand it fast. Please can you send me something because am a beginner and I really want to learn. Thank you.


  67. Hi Dev,
    We have 3 years of data in more than 30 tables based on Date partition. I mean we are creating one partion for each business day. Now we want the purge the data which is 18 months older. How do I efficiently purge the data ? Can you please help us on it in SQL Server 2005.


  68. Hi Dave,
    How Can I insert records on daily basis in a partitioned table.
    A data warehouse table which I update on daily basis now I created five partition on it one for each year and the last partition is empty.
    I need to know the best practice in this regard.


  69. Hi Dave,

    I am created partitioned table using datetime,
    I defined range for month upto one year ‘2011-01-01′,’2011-02-1′,’2011-03-1’
    And I created a Partitioned Table.

    when I Looking into sys.partitions

    SELECT partition_id, object_id, partition_number, rows
    FROM sys.partitions
    WHERE object_id = OBJECT_ID(‘partitionedtablet’)
    order by partition_Number


    *** I am getting twise or thrise of the partion_number of same number,
    How and why??

    And some having rows and other Partition row of same number does not have rows, but index column saying 0,1 and 2.

    Can anyone explain please……………..

    Ramesh Chandler.


  70. Dear pinal,
    I am regularly reading your sql blogs,
    the main problem in your blog is you are not specifying that which edition of sql server contain the particular concept. ex: partitioning is possible only in enterprise edition but you didnt mention it anywhere,
    So pls form next time keep in mind to tell edition of sql server.


  71. Here is an interesting question for you Dave,
    I have a partitioned table with a clustered index on start_dt which is a datetime type field.
    The same table has a few more datetime fields one of them being customer_call_start_date.
    When creating an index on this table and having a key on the customer_call_start_date is it better to write it this way:
    CREATE NONCLUSTERED INDEX [myIndex] ON [Schema].[table]
    [Column1] ASC,
    [customer_call_start_date] ASC
    ON [Partition_Scheme_Partition]([START_DT])

    or this way:
    CREATE NONCLUSTERED INDEX [myIndex] ON [Schema].[table]
    [Column1] ASC,
    [customer_call_start_date] ASC
    ON [Partition_Scheme_Partition]([customer_call_start_date])

    Thank you


  72. i have very big table which i want to implement partition as you explained but how should i truncate the data which is older than some period as automatic job


  73. Hi Dave, thanks for the simple tutorial. I just have a question which I’m not sure if you have answered it already. How does Partition help in performance when you have a normalized schema with 1 main table and 4 child tables all connected with a unique id (bigint).

    basically on our schema the important thing is a datetime field named WEEK because this determines the chronological order of records however this field is only present on the Main/Parent table. How does partitioning affect all our Query statements where they are joined together with the child tables?

    Thank you in advance.


    • Lian, We just went through this recently (see blog below for more details).
      When you apply your partition function sql server will reorganize the data into files to match your function. be aware that this can take some time!

      As new data is written into the database, is is written to the correct file/partition.


  74. Hi Dave, can you explain me how can I partition my data warehouse table it has got one clustered composite primary key [snapshot_date_key, process_date_Key, process_unit_key, metric_key, uom_key, msr_type_key] it has got around 14 million data and all its queries are so effective. we’d like to tune it.. hence we are looking for partitioning.
    kindly share your insights and thoughts about this? also revert me in case if you need additional information.


  75. Hey There. I found your blog using msn. This is a really well written article.
    I’ll be sure to bookmark it and come back to read more of your useful information. Thanks for the post. I’ll definitely return.


  76. Manoj

    I am new to DB, I have a database with 30gb single mdf file, and the perfromance of the database appears to be slow.

    I have below queries,

    1.how can i split the 30gb mdf file into many mdf files?, if this can be done, will this splitting spread the 30gb data accross multiple mdf/ndf files?

    2.I have tried adding the mdf/ndf files ,under database->properties->files, if Iuncheck the autogrowth of the 30gb mdf file, will the data automatically move to the newly added mdf/ndf files?

    3.can something be done in SQL, that when importing the .bak file of 30gb, data can be split accross multiple mdf/ndf files created prior to the restoration of the database backup file?

    Please help me.. Any help ASAP, will be a lifesaver for me


  77. Pingback: SQL SERVER – Weekly Series – Memory Lane – #013 « SQL Server Journey with SQL Authority

  78. Hi Pinal

    Is it possible to partition a table using 2 column value’s instead of only 1 column for the partition function?

    Consider a table with 4 columns

    ID (int, primary key,
    name (varchar),
    Country (varchar),
    region (varchar)

    ID name Country region

    1 rahul India karnataka
    2 raj India maharastra
    3 brad Usa california
    4 richard Usa arizona

    I want partition data combining based on country and region
    1st partition data like india and karnataka
    2nd partition data like india and maharastra.
    3rd partition data like usa and california
    4th partition data like usa and arizona


    • Yes it is possible, You need to use a Computed Column and Make sure it is Persisted Something like Country_Region and use the computed column to manage the partitions. It would be easy and efficient to use ID’s instead of VARCHAR


      • Hi Sir,
        I seen your article on Table partitioning in SQL Server,I have a requirement which is quiet tricky,I need your help with the Stored Procedure scripts for the below logic

        SQL Procedure having Loop never end by itself to populate the data to the below table,its a infinite loop,just the programmer need to stop the program abruptly,But data should exists in table
        table name : T_RECORD_DUMP
        COLUMN 2 : Insert_date – DATETIME COLUMN

        TABLE Having 4 partitions p1,p2,p3,p4

        Every 60sec the records to be switch between the partitions
        before switching to the other partition data should be truncated.

        p1 ——- p2 ———p3—–p4
        1stmin 2ndmin 3rdmin 4thmin

        at the end of 4th min records to be inserted to partition p1 again until 1min time frame BEFORE INSERT MUST TRUNCate(not use delete stATEMENT)

        In the above scenario,partition is based on time.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s