Posted by Erland Sommarskog on 07/22/05 00:31

jsfromynr (jatinder.singh@clovertechnologies.com) writes:
> Current Solution :
>
> 1) For each Entity I am having a column TID (Tracking ID) which
> keep on increasing for an instance of Entity. (so history and present
> data in the same table) . It makes querying the data difficult.
> 2) I am also using month year table for Managing data i.e
><TableNameMMYYYY> for a given month and year
>
> Purposed Soluion :
>
> 1) Using a seperate table so that history and present data is
> placed seperately .
>
> 2) Store All data in one table <TableName>
>
> Please guide me on advantages and disadvantages you pin point in the
> two approaches.

It's a little unclear what you mean, but anyway having a table for each
month is not a good idea. Well, if you need to distribute the data it
could be, but in such case you should unite the data in a partitioned
view, and all your queries should use that view.

As for having a current data in a separate table, and adding this
data to a history table at the end of a day in a maintenance job can
sometimes be useful. As a matter of fact, this is routine in our
system.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]