question

YogeshJ avatar image
YogeshJ asked

Difference between table and clustered index partition, and syntax?

Hi, **1) Is there any difference in 'table Partition' and 'Clustered index partition' ?** **2) Syntax of Alter Table to add Partition schema in that instead of creating new table with partition schema ?** As we need to mentioned the partition schema while declaring the table as shown below :- CREATE TABLE [dbo].[fact_warehouse_transaction]( [transaction_ident] [int] NOT NULL IDENTITY(1, 1), [natural_key] [decimal](18, 0) NULL, [dimension1_ident] [int] NULL, [dimension2_ident] [int] NULL, --… (many other columns) [transaction_date_and_time] [datetime] NOT NULL ) ON [Transaction_Warehouse_Partition_Scheme] (transaction_date_and_time) BUT, my problem is that , I already have this table (Suppose 'fact_warehouse_transaction' )in DB with primary key over column 'transaction_date_and_time' ,with millions of records. So i can't run create script instead i need to create Alter Table sort of script. Can you please suggest the syntax of alter to add this schema 'Transaction_Warehouse_Partition_Scheme' in it. OR is it so , that when i Re-create Clustered index of this table by binding it to the PARTITIONED SCHEMA , table will automatically be divided in to the different partition ? ex :- CREATE Clustered INDEX ix_transaction_warehouse_partitioned ON [dbo].[fact_warehouse_transaction] (transaction_date_and_time) ON Transaction_Warehouse_Partition_Scheme (transaction_date_and_time) IN short is 'partitioning Clustered index' and pratition table the same thing ? Please clarify. Thanks Yogesh
partitioningalter-table
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I summarized the title of the question a little. If I got it wrong please adjust.
0 Likes 0 ·

1 Answer

·
Sacred Jewel avatar image
Sacred Jewel answered
Your question is hard to understand. But it is quite simple that table partioning is indeed the clustered index partitioning (As clsutered index would be partitioned as well). For concrete and detailed info there is a very comprehensive document [here][1] [1]: http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/PartTableAndIndexStrat.docx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for replying .
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.