Databases
StackOverflow2013
Postsdbo
POHow to handle archiving for seasonal database values on SQL Server
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to handle archiving for seasonal database values on SQL Server
    primarykey
    data
    text
    <p>I am on <code>SQL Server 2008 R2</code> and I am currently developing a database structure which contains <strong>seasonal values</strong> for some products.</p> <p>By <strong>seasonal</strong> I mean that those values won't be useful after a particular date in terms of <em>customer</em> use. But, those values will be used for statistical results by <em>internal stuff</em>.</p> <p>On the sales web site, we will add a feature for product search and one of my aim is to make this search as optimized as possible. <em>But, more row inside the database table, less fast this search will become.</em> So, I consider archiving the unused values.</p> <p>I can handle auto archiving with SQL Server Jobs automatically. No problem there. But I am not sure how I should archive those values.</p> <p>Best way I can come up with is that I create another table inside the same database with same columns and put them there.</p> <blockquote> <p><strong>Example :</strong></p> <p>My main table name is <code>ProductPrices</code> and there a primary key has been defined for this database. Then, I have created another table named <code>ProdutcPrices_archive</code>. I created a primary key field for this table as well and the same columns as <code>ProductPrices</code> table except for <code>ProdutPrices</code> primary key value. I don't think it is useful to archive that value <strong>(do I think correct?)</strong>.</p> <p>For the internal use, I consider putting two table values together with <code>UNION</code> <strong>(Is that the correct way?)</strong>.</p> </blockquote> <p>This database is meant to use for long time and it should be designed with best structure. I am not sure if I miss something here for the long run.</p> <p>Any advice would be appreciated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UStugberk
    1. UStugberk
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. CO"But, more row inside the database table, less fast this search will become" - possibly, but not necessarily. The design you describe suggests that all access to the table will go through a foreign key to "products"; in this case, you will almost certainly not notice a reduction in speed by inserting more data into the same table. I wouldn't even begin to optimize for this scenario until you can prove that you have a performance problem - I like to populate my database with 10 times more test data than expected in reality, and then measure.
      singulars
      1. POHow to handle archiving for seasonal database values on SQL Server
      1. USNeville Kuyt
    2. COFollow below link to check auto archival process. http://stackoverflow.com/questions/18908903/auto-maintain-archive-purge-purge-on-source-table-in-sql-server
      singulars
      1. POHow to handle archiving for seasonal database values on SQL Server
      1. USAsif Ghanchi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload