I saw that ther is a wizard for partitioning, I am using Sql-Server 2014, can't find it.Įxcellent post. My question is what will happen to the data in this case, All the data for the table will move to the ne file group? If I create a seperate filegroup and partition scheme is create on the new file group and followed by the Clustered index on the Partition scheme. I just have a question that I have a large table in the PRIMARY file group. Here is the script that we can use in our scenario. Keep in mind that you may see some performance degradation while the index is being rebuilt using the ONLINE option.

Finally, if you are concerned about the downtime required to perform this task and you are using SQL Server Enterprise Edition you could use the ONLINE=ON option of the CREATE INDEX statement to minimize any downtime for your application. Similarly, if our table had a clustered index defined, but it was defined on same column that we plan to partition the table on we could run the CREATE CLUSTERED INDEX statement with the DROP_EXISTING clause. If our table did not have a clustered index we could omit this step and just run the CREATE CLUSTERED INDEX statement. Since we are going to partition the table using a clustered index and our table already has a clustered index defined we'll need to drop this index first and recreate the constraint using a non-clustered index. Now that we have a partition scheme we can go ahead and partition our table. INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id CREATE PARTITION FUNCTION myDateRangePF (datetime)ĪS RANGE RIGHT FOR VALUES ('20110101', '20120101','20130101')ĬREATE PARTITION SCHEME myPartitionSchemeĪS PARTITION myDateRangePF ALL TO ()

Sql server partition by