Table Partition

Question

Table Partition

hello_san

Old Hand

Points: 328
More actions
February 5, 2014 at 2:15 pm

#303778

1.We have a daily load table called ‘X'in one of our databases which contains around 356 million records.
2.We plan to archive data to another table anything older than 180 days.
3.In order to achieve that we plan to create another table called “Y” table.
4.On a daily basis we need to move the data in Y table. Data is retained in this table and not to be truncated.
Please suggest me if you have any good ideas.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

hello_san (2/5/2014)
1.We have a daily load table called ‘X'in one of our databases which contains around 356 million records.
2.We plan to archive data to another table anything older than 180 days.
3.In order to achieve that we plan to create another table called “Y” table.
4.On a daily basis we need to move the data in Y table. Data is retained in this table and not to be truncated.
Please suggest me if you have any good ideas.

1. Which Edition of SQL Server do you have?

2. How many months of information is currently in the table?

3. What is the largest number of rows that you have for any given month?

4. What is the current size of the table and the individual indexes?

5. Do any other table reference the table in question using FKs?

6. How much extra space do you have on the disk(s) this table is on?

7. Do you have separate disks for the archives?

8. Are rows in this table (other than the current month) EVER updated?

9. What columns and datatypes is the PK based on?

10. Is there a DATETIME column in the table which represents the date and time the row was inserted into the table?

11. Other than the PK, are there any other UNIQUE columns in the table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)