1

What is the best practice of keeping past data in the database? For example lets say we have transaction tables like AttendanceTrans, SalaryTrans in a payroll solution. So every month we have to insert hundreds or thousands of new records to these tables. So all the past and current data in same table.

Another approach would be to keep AttendanceHistory and SalaryHistory tables. So that end of every period (month) we empty the Trans tables after coping the data to respective History tables.

When considering factors like performance, ease of report generation, ease of coding and maintenance, what would be the optimum solution?

Note : RDBMS is SQL Server 2008 and programming environment is .NET (C#)

CAD
  • 4,112
  • 6
  • 29
  • 47
  • 2
    Did you look at the questions in the Related column on the right? However, every answer here depends on your requirements and it will be highly opinion based. – Steve May 31 '14 at 10:29

2 Answers2

3

In general you should keep all the data in the same table. SQL Server is great at handling large volumes of data and it will make your life a lot easier (reporting, querying, coding, maintenance) if it's all in one place. If your data is indexed appropriately then you'll be just fine with thousands of new records per month.

Rory
  • 40,559
  • 52
  • 175
  • 261
1

In my opinion, best solution in sql server is CDC (Change Data Capture). It very simple to use. You can change volume of historical data with changing schedule of clear job. I think this is the best way for performance because CDC gets changes from transaction log (it is not triggers on table), but you need to use Full Recovery Model for you database.

Vladimir Semashkin
  • 1,270
  • 1
  • 10
  • 21
  • Is it available in 2008 version? – CAD May 31 '14 at 13:02
  • CDC is for a scenario where you want to keep track of changes to existing data. The question is about accumulating transactional data and whether to keep it in a single table or separate. CDC is also way more complicated than you'd want to look at for just a few thousand rows per month on a couple of tables in a single db. – Rory Jun 01 '14 at 09:34