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;
GO
--- Step 1 : Create New Test Database with two different filegroups.
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'TestDB')
DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON PRIMARY
(NAME='TestDB_Part1',
FILENAME=
'C:\Data\Primary\TestDB_Part1.mdf',
SIZE=2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = 'TestDB_Part2',
FILENAME =
'C:\Data\Secondary\TestDB_Part2.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 );
GO


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.
USE TestDB;
GO
--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION TestDB_PartitionRange (INT)
AS RANGE LEFT FOR
VALUES
(10);
GO

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.
USE TestDB;
GO
--- Step 3 : Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2);
GO

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.
USE TestDB;
GO
--- Step 4 : Create Table with Partition Key and Partition Scheme
CREATE TABLE TestTable
(ID INT NOT NULL,
Date DATETIME)
ON TestDB_PartitionScheme (ID);
GO

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.
USE TestDB;
GO
--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table
CREATE UNIQUE CLUSTERED INDEX IX_TestTable
ON TestTable(ID)
ON TestDB_PartitionScheme (ID);
GO

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.
USE TestDB;
GO
--- Step 6 : Insert Data in Partitioned Table
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
VALUES (1,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (11,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (12,GETDATE());
GO

Step 7 : Test Data from TestTable
Query TestTable and see the values inserted in TestTable.
USE TestDB;
GO
--- Step 7 : Test Data from TestTable
SELECT *
FROM TestTable;
GO

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.
USE TestDB;
GO
--- Step 8 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO


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)

About these ads

127 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.

    Thanks.

  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.

    Thanks!!

  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

    thanks

  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.

    Regards,
    Ritesh

  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?

    Greetings,
    Sigurd

  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

    Regards,
    Hooman

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

    CREATE PARTITION FUNCTION ParitioningTest_PartitionRange(nchar(4))
    AS
    RANGE LEFT FOR VALUES (‘CARD’, ‘CBO’, ‘CHEQ’, ‘CUST’, ‘TELL’)

    CREATE PARTITION SCHEME ParitioningTest_PartitionScheme
    AS PARTITION ParitioningTest_PartitionRange
    TO ([FG_CARD], [FG_CBO],
    [FG_CHEQ],[FG_CUST],
    [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?

    Regards,
    Hooman

  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.

    Regards
    Ajay

  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,
    Anoop

  18. ‘C:\Data\Primary\TestDB_Part1.mdf’,
    SIZE=2,
    MAXSIZE=100,
    FILEGROWTH=1 ),
    FILEGROUP TestDB_Part2
    (NAME = ‘TestDB_Part2′,
    FILENAME =
    ‘C:\Data\Secondary\TestDB_Part2.ndf‘,

    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?

    Example:

    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
    example
    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!

    Thanks
    Brijesh

  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 (
    Standard,
    School,
    Board
    )
    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.

    Thanks
    Shital

  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.

    Thanks

  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.

    Imran

  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.

    Sanjay

  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
    Rupa

  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.

    Regards

    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
    WHERE OBJECT_NAME(OBJECT_ID)=’TestTable’;

    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 ?

    thanks,
    jagan

  42. i m running the code for partition as

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

    go

    use Northwind
    create partition function partfunc1 (int)
    as
    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
    go
    create database testdb
    on primary
    (name=test_part1, filename=’c:\partitionpractice\test_part1.mdf’,size=4mb,maxsize=100,filegrowth=1),
    filegroup testdb_part2
    (name=testdb_part2,filename=’c:\partitionpractice\testdb_part2.mdf’,size=2mb,maxsize=100,filegrowth=1)

    go

    use Northwind
    create partition function partfunc1 (int)
    as
    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

    • 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..?

    regards
    Sharath

  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?

    example:

    table 1
    id1 (PK)
    filed1
    filed2
    filed3
    filed4
    update_dm
    insert_dm

    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:

    http://connect.microsoft.com/SQLServer/feedback/details/240968/partition-table-using-min-max-functions-and-top-n-index-selection-and-performance

    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

    sys.partition_range_values
    sys.partition_schemes
    sys.partition_parameters
    sys.partition_functions
    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
    l:\data\shop_log.ldf.

    I have to convert my database to

    c:\data\shop.mdf
    l:\data\shop_log.ldf

    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…

    Regards

    Mark.

  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

    Thanks.

    Will

  56. HI PINALKUMAR DAVE,
    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 !

    Kevin

  57. Here is the complete solution:

    ————————————————————————————————-

    – CREATE DATABASE
    CREATE DATABASE TUTORIAL
    GO

    ————————————————————————————————-

    USE TUTORIAL
    GO
    CREATE PROCEDURE [dbo].[sp_dim_date_monthly_partition] @nRunDate datetime = NULL
    AS
    BEGIN

    SET NOCOUNT ON
    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’

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

    ————————————————————————————————-

    – ADD FILEGROUP
    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 )

    ————————————————————————————————-

    – ADD FILE TO FILEGROUP
    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’, ”” ) + ‘,
    SIZE = 3072 KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB
    ) TO FILEGROUP ‘ + @fg_name
    EXEC( @file_sql )

    ————————————————————————————————-

    – CREATE PARTITION FUNCTION
    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 )

    ————————————————————————————————-

    – CREATE PARTITION SCHEME
    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)
    – CREATE A TABLE tblsaledate TO USE PARTITION SCHEME
    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)
    BEGIN
    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)
    END

    —————————————————————————————————

    – SET NEW FILE TO BE USED FOR NEXT SET OF DATA
    DECLARE @aps_sql nvarchar(max)
    SET @aps_sql =
    'ALTER PARTITION SCHEME ' + QuoteName(@ps_name,'') + ' NEXT USED ' + @fg_name
    EXEC( @aps_sql )

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

    —————————————————————————————————

    – IMPLEMENT ERROR HANDLING
    SET @ErrCount = @@ERROR
    IF (@ErrCount 0)
    BEGIN
    RETURN @cRtnFailed
    END
    SET NOCOUNT OFF;
    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]

    */
    —————————————————————————————————

    END

  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’)
    DROP DATABASE 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.

      Thanks
      Manish

  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.
    Alia

  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
    Andrea

  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.
    -Vivek

  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)

    ex:
    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

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