locked
Table Creation based date RRS feed

  • Question

  •  i have one transaction table with 80 columns and day wise record growth will be high(150000 Records), shall i create a single table or day wise table
     
      
     Sample :
     
    CREATE TABLE CDRInfo( ID INT IDENTITY(1,1), Customer_id INT,  Amount FLOAT ) -- single Table Sample :
     
    CREATE TABLE CDRInfo_20140919( ID INT IDENTITY(1,1), Customer_id INT,  Amount FLOAT ) -- Day wise


    frequently i need to retreive the customer information. which approach is best to proceed
    Thursday, September 18, 2014 8:03 AM

Answers

  • Hi ,

    Creating multiple tables at date level is not a good idea at all. It will be very tough to maintain. Also if you need to retrieve the data for two are more dates you need to do a dynamic union all. You keep the data in same table and create partitions on each date. Also 150000 Records is not a huge data volume and SQL Server can handle this well with appropriate indexes and partitioning. If you need to store the data for multiple years , you can consider splitting this into two tables 1) Current - will store the current data i.e, for an year 2) Archive - Which will store the historical data other than current data.


    Best Regards Sorna

    • Proposed as answer by ImranKazi Thursday, September 18, 2014 8:54 AM
    • Marked as answer by Parivallal S Thursday, September 18, 2014 9:47 AM
    Thursday, September 18, 2014 8:39 AM

All replies

  • Single table should be enough. You may choose to partition it by date field if you want

    http://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, September 18, 2014 8:34 AM
  • Hi ,

    Creating multiple tables at date level is not a good idea at all. It will be very tough to maintain. Also if you need to retrieve the data for two are more dates you need to do a dynamic union all. You keep the data in same table and create partitions on each date. Also 150000 Records is not a huge data volume and SQL Server can handle this well with appropriate indexes and partitioning. If you need to store the data for multiple years , you can consider splitting this into two tables 1) Current - will store the current data i.e, for an year 2) Archive - Which will store the historical data other than current data.


    Best Regards Sorna

    • Proposed as answer by ImranKazi Thursday, September 18, 2014 8:54 AM
    • Marked as answer by Parivallal S Thursday, September 18, 2014 9:47 AM
    Thursday, September 18, 2014 8:39 AM