Home > Partitioning, SQLServer > SQL Server – How to partition an existing table…

SQL Server – How to partition an existing table…

It’s fairly simple, create a CLUSTERED INDEX on the table. While creating a clustered index you need to specify the partition scheme to be used in ON clause of CREATE INDEX statement.

 

We still need to create a partition function and a partition scheme, I have posted earlier about how to create the same here. Step 1 – Creating a partition function and Step 2 – Creating a partition scheme are same, only Step 3 changes as we are partitioning an existing table.

 

For example, I have a table dbo.Table_Orders which is not partitioned:

SELECT      OrderID, OrderDate, Quantity, Amount

FROM        dbo.Table_Orders

 

SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

Result Set:

OrderID     OrderDate  Quantity    Amount

———– ———- ———– ———————

1           2011-07-22 34          3200.00

2           2010-06-22 98          9800.00

3           2009-05-22 65          6500.00

4           2011-07-24 73          7300.00

 

(4 row(s) affected)

 

partition_id         object_id   partition_number rows

——————– ———– —————- ——————–

72057594039631872    5575058     1                4

 

(1 row(s) affected)

 

Now, if we want to partition this table, we need to create a clustered index ON a partition scheme. I have a partition scheme in my database ordersPartScheme. So the clustered index should be created using:

CREATE CLUSTERED INDEX [Clust_Orders] ON [dbo].[Table_Orders]

(

      [OrderID] ASC

) ON ordersPartScheme(OrderDate)

 

– Check for new partitions

SELECT      partition_id, object_id, partition_number, rows

FROM        sys.partitions

WHERE       object_id = OBJECT_ID('Table_Orders')

Result Set:

partition_id         object_id   partition_number rows

——————– ———– —————- ——————–

72057594039762944    5575058     1                1

72057594039828480    5575058     2                3

 

(2 row(s) affected)

From the result set, we can see that new partition is added to the table, and required rows are also moved to new partition.

 

However, if you already have a clustered index created on a table, then you can use DROP_EXISTING clause to re-create the clustered index:

CREATE CLUSTERED INDEX [Clust_Orders] ON [dbo].[Table_Orders]

(

      [OrderID] ASC

) WITH (DROP_EXISTING = ON)

ON ordersPartScheme(OrderDate)

Using DROP_EXISTING = ON specifies that the index is dropped and rebuilt.

For more information, check Horizontally partitioning a SQL Server Database Table.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
Categories: Partitioning, SQLServer
  1. suresh
    June 21, 2012 at 7:11 am

    in table partitioning,can we drop clustered index . non clustered index is it possible.non clustered is possible give me one example.

    • June 25, 2012 at 6:43 pm

      Hi Suresh,

      Apologies for delayed response.

      You can surely drop a clustered index on a partitioned table. You partition a table based on a column, it does’t need to have a clustered index.

      • suresh
        July 4, 2012 at 11:22 am

        Hi Vishal,
        i tried partitionit is ok.but clustered index is changed to non clustered.why give me one example.please mail me my mail.

  2. suresh
    September 14, 2012 at 8:50 am

    Hi Vishal,
    i faced two issues in partitioning
    1)when i am doing partitioning in primary key,clustered index is not changing.but always i am not dependnig primay key.so
    2)some times i depend othr columes EX: date column, when i am doing partition on date column ,partitioning is ok but clustered index is changed automatically nonclustered index. why it will happen? this time no clistered index is there in my table so duplicates will hapen.
    Please give a suggestion ASAP.

  3. Ionut
    October 25, 2012 at 4:49 pm

    Hey Vishal,
    I was just wondering, is there any other way to partition an existing table without creating a clustered index on it ? Maybe I have a table that I don’t want to be indexed.
    Thank you, and really great articles man !

  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 264 other followers

%d bloggers like this: