Posted by Erland Sommarskog on 07/25/05 12:33

Zero.NULL (manish19@gmail.com) writes:
> Erland, I apologize to use a regional unit here. Lac is equal to 0.1
> Million
>
> Now let me clear the implementation, when a new batch transaction takes
> place, the data for this transaction get stored in the current Month
> year table (for now XYZMast072005) and when we update this batch it
> insert updated data in the same table (this is being done to keep the
> History of previous data). Now the issue is the batch in the previous
> Month Year table (for now XYZMast062005) can also be updated, hence
> data grows in the same table. (We have managed the Primary key IDs, and
> use a base id for each and every batch). So this is why it is not
> possible to supply a fixed range on Primary key column on these tables.

I don't think so. It is not clear to me what your primary key is, but
it appears that it is (batchid, runningnumber). But you have fooled
yourself. Because in fact the primary key is (yearmonth, batchid,
runningnumber). You have hidden that first component of the key in
the table name. Thus, you need to add a column with yearmonth (that is
'200507', not '072005'!), then you can partition on that column.

However, now that I know what a Lac is, I would suggest that you should
leave this yearmonth-table business entirely. You get 200000-400000 rows
a month. That is not a threating size, and having one table per month
is definintely going to buy you more headache than it will save you from.
One table per year possibly, but not even 48 milliion rows really call
for a partition. It depends a little on what requirements for how long
you have to save the data.

I reckon that if most operations are against current month, it could
still be worthwhile to have an area for the current month. This can
be achieved in two ways. One is to have two tables, currentmonth and
archive. By the end of the month you move over the data from the
currentmonth table to the archive. You could unify the tables with a
partitioned view, assuming that you put a date first in the PK.

The other alternative would be to have all data in one table, but then
have an indexed view which is defined to hold the values of the current
month. Queries that are for current month only could go directly against
that view. Queries that are unlimited would go against the base table.
In this case, you would need a monthly job that drops the view and
recreates it with a new defintion. One thing which is appealing here is
that since the view would always be empty initially, this would not
require any data to be moved. (Not that moving half a million rows is
daunting.)

I should add, that all these designs I discuss here requires proper
indexing. Then again, if you were to search for data in your current
design, and you don't know which month to look in, you would have a hard
time to find what you are looking for.

--
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]