separate history table vs different partition in same table(partition name history) RRS feed

  • Question

  • Hi All, I have two table design as follows, i need to get performance wise experience you all having following table design ,

    Note : I need to information following two design considering performance, not solution like "change data capture"

    Design 01

    CREATE TABLE Customer(custID int primary Key, CustName varchar(100), status bit); 
    CREATE TABLE Customer_His(CustVer int identity(1,1),custID int, CustName varchar(100), status bit); 

    if customers edited then Org. record move to "Customer_His" table and delete from "Customer" table, and add new record for same "custID". (need to maintain history table Delete, and two insert) 

     Design 02

    CREATE TABLE Customer(custPK int identity(1,1) primary Key,custID int/*cust id duplicated*/, CustName varchar(100), status bit, Cust_Latest_ver bit--if 1 its latest 0 mean old); 
    --TABLE has two partition like,
    "SELECT custID FROM Customer WHERE CustLatestver=1" -> partition01
    "SELECT custID FROM Customer WHERE CustLatestver=0" -> partitionHis

    if customers edited then Org. record move to "partitionHis" and  add new record for same "custID". (Update and Insert)

    Thanks & regards,
    Tharindu Dhaneenja (www.databaseusergroup.com)

    Friday, March 17, 2017 7:19 AM


All replies