|
|
Posted by DickChristoph on 05/04/06 13:26
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]
|