sql server table partitioning
Scenario:

 - 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?

   

 
sql server table partitioning
===

Scenario:

 - 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?

   

 