I am in situation here. I want to partition a table week wise and keep the data of last 4 weeks in the table. I ll be merging the oldest partition every week and adding another partition for the new records.
I thought I can partition the table on the date column in the table and it does work. My question how'll i create the clustered index in the table. I want to create on the Identity column. If I make the index this would again merge all the data and there
would not be any partitioning left.
Can you please suggest me how will it work ? Or i have to change the partition function on some INT values instead of the date time.
Please give your valuable advices......