Results 1 to 2 of 2

Thread: two questions

  1. #1
    Join Date
    Jul 2003

    Unanswered: two questions

    First I am using merge replication and sometimes it is nessesary to make a big delete or update for example 500 000 rows is it possible to send this to subscribers just like stored procedure if not which is the best way to do so,

    Second I have a table "transactions" with 20,000,000 rows is it better to seperate into this tables"deposits", "withdrawals", "transfers", "loans", "payments" or to keep just in one table

    thanks in advance

  2. #2
    Join Date
    Oct 2001
    Naples, FL
    On merge rep. (FROM BOL)

    Changed data values are replicated to other sites and converged with changes made at those sites only when synchronization occurs. Synchronizations can occur minutes, days, or even weeks apart and are defined in the Merge Agent schedule. Data is converged and all sites ultimately end up with the same data values, but for this to happen, you would have to stop all updates and merge between sites a couple of times.

    That says to me that it would be fine to change the data at both places at the same time without waiting for synch to occur. I haven't used merge very much so there may be someone who disagrees.

    On your second question the answer is "it depends". Normalization is important to control data integrity and reduce data redundancy. For instance take a typical orders type database. On a single order I may have 20 items. If I put this in one table it might look like this.

    OrderID - ItemID - CustName - CustAddress - ItemAmount - etc.
    1 1 Joe Main st 5.00
    1 2 Joe Main st 2.00
    1 3 Joe Main st 1.00
    1 4 "" "" '"

    And so on, here you see order id repeated to record all itemID's, you also see colums that could depend on the key repeated (custname, address, etc.) Breaking this out into two tables would be better for redundancy

    OrderID - CustName - CustAddress - OrderAmount
    1 Joe Main st 8.00

    ItemID OrderID ItemAmount
    1 1 5.00
    2 1 2.00
    3 1 1.00

    Less repeating, less pages to read on queries, when customers need to run reports on orders and don't need to see the details, pulling from table 1 will be faster than if you had stored all data in it.

    Ray Higdon MCSE, MCDBA, CCNA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts