|
|
Posted by DickChristoph on 05/04/06 13:45
Hi Sammeer,
I just realized I may not may answered your question.
Why are you partitioning these tables? Are they for example separate
companyids where you want to group certain ones on certain tables? Are the
tables distributed on different servers or in different file groups? What
characteristic defines your partitioning logic?
If they are different companies for example you could store a partioncolumn
in a company table and then associate the company_id with a partioncolumn in
a 1 to 1 correspondence (where the parition_id would be in a specific range)
Something like
Company_ID PartionColumn
10 1
100 3
1000 2
10000 4
> CONSTRAINT [check_PartitionColumn1] CHECK (PartitionColumn in (1,2))
> CONSTRAINT [check_PartitionColumn2] CHECK (PartitionColumn in (3, 4))
-Dick
--
"DickChristoph" <dchristo99@yahoo.com> wrote in message
news:fFk6g.32$EC.22@tornado.rdc-kc.rr.com...
> Hi Sameer,
>
> The SQL Script below works to create a partioned updateable view
>
> It Uses these check constraints
> CONSTRAINT [check_PartitionColumn1] CHECK (PartitionColumn in (1,100))
> CONSTRAINT [check_PartitionColumn2] CHECK (PartitionColumn in (1000,
> 10000))
>
> But if you change the check constraints to
>
> CONSTRAINT [check_PartitionColumn1] CHECK (PartitionColumn in
> (1,100,1000))
> CONSTRAINT [check_PartitionColumn2] CHECK (PartitionColumn in
> (2,100,1000))
>
> It won't work becase (according to BOL)
>
> "The key ranges of the CHECK constraints in each table do not overlap
> with the ranges of any other table"
>
> Some other things worth noting:
>
> Partitioning columns cannot allow nulls.
> Partitioning columns must be a part of the primary key of the table.
> Partitioning columns cannot include identity
>
>
> This works
>
> CREATE TABLE [MyPartionTest1] (
> [RowId] [int] NOT NULL ,
> [PartitionColumn] int NOT NULL ,
> [Data] [varchar] (20) NULL ,
> CONSTRAINT [PK_MyPartionTest1] PRIMARY KEY CLUSTERED
> (
> [RowId],
> [PartitionColumn]
> ) ON [PRIMARY] ,
> CONSTRAINT [check_PartitionColumn1] CHECK (PartitionColumn in (1,100))
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [MyPartionTest2] (
> [RowId] [int] NOT NULL ,
> [PartitionColumn] int NOT NULL ,
> [Data] [varchar] (20) NULL ,
> CONSTRAINT [PK_MyPartionTest2] PRIMARY KEY CLUSTERED
> (
> [RowId],
> [PartitionColumn]
> ) ON [PRIMARY] ,
> CONSTRAINT [check_PartitionColumn2] CHECK (PartitionColumn in (1000,
> 10000))
> ) ON [PRIMARY]
> GO
>
> go
>
> Create View MyPartionTest
> as
> select * from MyPartionTest1
> union all
> select * from MyPartionTest2
>
> go
>
> insert MyPartionTest Values(1,1,'Data 1-100')
>
> --
> -Dick Christoph
> dchristo@mn.rr.com
> 612-724-9282
> "sameer_deshpande" <sameer_deshpande@hotmail.com> wrote in message
> news:1146733086.482632.96930@u72g2000cwu.googlegroups.com...
>> Hi,
>>
>> I need to create a partition table but the column on which I need to
>> create a partition may not have any logical ranges. So while creating
>> or defining partition function I can not use any range.
>>
>> like
>>
>> CREATE PARTITION FUNCTION my_part_func (NUMERIC(7)) AS RANGE LEFT FOR
>> VALUES (1,100,1000);
>>
>> Is there any way to define partition function in SQL Server something
>> like Oracle HASH partitions where logical range is unkown?
>>
>> Thanks
>>
>> Sameer
>>
>
>
Navigation:
[Reply to this message]
|