|
|
Posted by DickChristoph on 05/04/06 14:58
Hi Sameer
Well then how about creating 5 Partioned tables and set the Check constriant
of the Partition Colum to be 0 to 4
Each each table definition
Table0
> CONSTRAINT [check_PartitionColumn0] CHECK (PartitionColumn = 0)
Table1
> CONSTRAINT [check_PartitionColumn1] CHECK (PartitionColumn = 1)
Table2
> CONSTRAINT [check_PartitionColumn2] CHECK (PartitionColumn = 2)
Table3
> CONSTRAINT [check_PartitionColumn3] CHECK (PartitionColumn = 3)
Table4
> CONSTRAINT [check_PartitionColumn4] CHECK (PartitionColumn = 4)
Then when you insert into this partioned view insert ProjectID % 5 into the
Partition Column
% is the SQL Server Modulus operator and deliver the integer remainder after
dividing by 5 in this case.
--
-Dick Christoph
"sameer_deshpande" <sameer_deshpande@hotmail.com> wrote in message
news:1146742117.996286.179820@v46g2000cwv.googlegroups.com...
> Hi Dick,
>
> Bit more info... I have a table where I store different projects
> information. The project id generated using a sequence. The max project
> Id will be 9999999. For me it will be of no use to partition on
> project_id column because there won't be more than 1000 projects.
>
> Now one can say, if there are no more than 1000 project, then why do I
> need partitions? The answer is, there are children's attached to this
> project. And children's are too many.
>
> So idea was to use something like a HASH function algorithm for
> project_id column, which will partition and will spread data to
> different partitions.
>
> f.ex: my Oracle script looks like
>
> CREATE TABLE x (id NUMBER; project_id NUMBER(7), child_id NUMBER
> )
> PARTITION BY HASH (project_id)
> (
> PARTITION P01,
> PARTITION P02,
> PARTITION P03);
>
> So here Oracle used HASH algorithm and my data is spread across
> different partitions.
>
> I need compatible SQL Server script, which will do so. But to create
> partition tables, I need to create partition function, which defines
> the RANGE. And in my case I can define range but it wil be of no use.
>
> Sameer
>
Navigation:
[Reply to this message]
|