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