[[ function(logical boundary) -- scheme(physical FG location ) --> filegroup ]] create partition function myfn(int) as RANGE RIGHT FOR VALUES(2001,2002,2003,2004) ~ 2001 to 2002, 2002 - 2003, 2003 - 2004 * no.of partitions = no.of boundaries + 1 (because it covers the complete range of possible data values from -ve to +ve infinity) ------------- create partition scheme myprtnschm as PARTITION myfn to FG_1 , FG,FG3 ------------ create table mytable ( iid int , tid int check >100 <=400) on myprtnschm(tid) ------------ select $partition.myfn(tid) from mytable ----------------------------------------------------------------------------------------------------- Index on partitioning : now the partition is on TID but the pk is IID , but the index has to start with TID and its not a good practise to create CI on only TID, so create CL on both columns but starts with TID...create clustered index xx (TID,IID) * best practise : leave a file group at first for right partition. Leave a file group at last for the left partition. ----------------------------------------------------------------------------------------------------------------------------------------- Rolling Range or Sliding Window when new data adds to a very large table , lot of defragmentation will happen but if the requirement is to add the data into a partitioned table for a set of new partitioned amount of data eg:. currently partion is for 4 years.. 2001,2002,2003,2004... if you want to add 2005 data means archiving 2001 data then do the below. create 2 staging tables for year 2001(to archive and no check is required) and year 2005 ( to switch from partition 2001 to 2005 and check constraint is required) EXEC sp_resetstatus 'yourDBname'; ALTER DATABASE yourDBname SET EMERGENCY DBCC checkdb('yourDBname') ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE yourDBname SET MULTI_USER [do a piecimal restore] --> go to the error log --> some time a ndf file might corrupt-->in 2005 you can do piecimal restore--> alter database modify file my_ndf = offline --> alter database set online --> restore database FILE = MY_NDF with no_recovery ( here you are restoring the ndf only from old backup and what about the transactions --> so take full T-log backup and restore with recovery.