Post reply

Database design of more years

  • October 29, 2014 at 5:03 am

    #312091

    Hello,

    I have question about dividing of databases.

    Old supplier created separate database for each year + currently month

    it means, we have 7databases from 2008 + currently = 8

    every index, which I want to edit is needed alter in all databases and etc...query is not good and etc.

    But this time supplier will not be here, and now I am thinking about all this move to one archive database + currently - almost all things are better, but few not

    Almost every year we added column or change design according to requirements of customer, and one times I added column to 20milions rows table and not good.

    I am thinking about it,

    - comprimize can be on both, so there will be smaller size, +1 both

    - optimize on index is better on one than more, but with some creating script can be ok on both but ok, +1 for one database

    - separate files for every year database, but I can create for one, also separate files. , +1both

    - change design on table is better with smaller size of table than one, +1 for separate databases,

    So now I dont know, can anybody let me know your experience with this ?

    Thank you very much


    How to post data/code on a forum to get the best help: Option 1[/url] / Option 2[/url]

  • October 29, 2014 at 5:07 am

    #1755466

    My Suggestion would be to use Table Partitioning instead of new DB creation every year.

  • October 29, 2014 at 5:19 am

    #1755467

    vincyf1 (10/29/2014)

    My Suggestion would be to use Table Partitioning instead of new DB creation every year.

    I know this function, but I tried one times if I learned sql 2years ago. I remember for this.

    Do you have any experience with this ?

    So one Database, table partitioning. but is needed exchange something in query? directly in Insert, if I have to specify where I want to insert/delete data. or it will be automaticall into last?

    Do you have any good reading about this ? thanks for suggestion


    How to post data/code on a forum to get the best help: Option 1[/url] / Option 2[/url]

  • October 29, 2014 at 8:02 pm

    #1755665

    tony28 (10/29/2014)

    vincyf1 (10/29/2014)

    My Suggestion would be to use Table Partitioning instead of new DB creation every year.

    I know this function, but I tried one times if I learned sql 2years ago. I remember for this.

    Do you have any experience with this ?

    So one Database, table partitioning. but is needed exchange something in query? directly in Insert, if I have to specify where I want to insert/delete data. or it will be automaticall into last?

    Do you have any good reading about this ? thanks for suggestion

    Let's first ask a couple of questions.

    1. Do you have the Enterprise Edition or the Standard Edition?

    2. Once you write a row to the "current" table, is the row EVER modified?

    3. Are the rows of any of the older months and years EVER modified?

    4. Do you have any foreign keys pointing at the table?

    5. Do the tables have ALL the same columns, ALL the same indexes, ALL the same Primary Key , and ALL have a DATETIME column when the row was entered?

    --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)
    Intro to Tally Tables and Functions

  • October 29, 2014 at 11:56 pm

    #1755683

    Jeff Moden (10/29/2014)

    tony28 (10/29/2014)

    vincyf1 (10/29/2014)

    My Suggestion would be to use Table Partitioning instead of new DB creation every year.

    I know this function, but I tried one times if I learned sql 2years ago. I remember for this.

    Do you have any experience with this ?

    So one Database, table partitioning. but is needed exchange something in query? directly in Insert, if I have to specify where I want to insert/delete data. or it will be automaticall into last?

    Do you have any good reading about this ? thanks for suggestion

    Let's first ask a couple of questions.

    1. Do you have the Enterprise Edition or the Standard Edition?

    2. Once you write a row to the "current" table, is the row EVER modified?

    3. Are the rows of any of the older months and years EVER modified?

    4. Do you have any foreign keys pointing at the table?

    5. Do the tables have ALL the same columns, ALL the same indexes, ALL the same Primary Key , and ALL have a DATETIME column when the row was entered?

    Hello

    1.Enterprise

    2. Yes, there is few updates, not in all tables, but in few yes

    3. No Data are modified just this day, it means we work just with real data - data older than one day are just archiving -- we receive order, we create data according to this order, then we work with our data and after complete work data are updated for example to OK, or any value to this rows.

    4. no there isnt

    5. in years database , yes there are. same indexes I think will not be, same PK will be, datetime column same, but I told about if in new year I will have to add new column ,

    easier is add column in one database in one year(example 2015) than in one database for all years (2008-2015) , there will be modify of zero or few rows, there will be modify of 25milion rows... but next is that for example union all will not function without same columns. So this is my point now, one or more? and then normal or partitioning ?

    I started copy all data to one database, but during this I was thinking about this solution, how I will do this if customer will want to add new value(new column) , I will add to all rows? to 25milions rows? What sql server will do, will be ok with disk storage ? this is recreating table, with moving data, lot of data...

    thanks


    How to post data/code on a forum to get the best help: Option 1[/url] / Option 2[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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