- two databases: DB_A and DB_Archive with one very big table called tableA.
- every day, records older than 60 days are deleted from DB_A and moved to DB_Archive mainly to leave thing "separated" because tableA is heavily queried on DB_A for records of the past 2 months.
I want to get rid of this process because it is slow and consumes a lot of resources. I'm thinking of implementing table partitioning on DB_A with a partition function on a date column and storing all records < 2 month on one partition and all records > 2 months on another partition. My questions:
- is this scenario going to behave like if I had 2 different databases? If I query my tableA for records > getdate() - 30, is it going to read the archiving partition?
- I supposed I have to partition the indexes as well, right?
- How do I deal with the fact that tomorrow my partition function will "change", I mean, if I create the function today (2nd of July, its range will be 2nd of May, but tomorrow would be the 3rd of May). Can I create a dynamic partition function?