Partition function - Unknow range column

[sameer_deshpande]

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

[DickChristoph]

Re: Partition function - Unknow range column

Hi Sameer,

The SQL Script below works to create a partioned updateable view

It Uses these check constraints
CONSTRAINT [check_Partition Column1] CHECK (PartitionColum n in (1,100))
CONSTRAINT [check_Partition Column2] CHECK (PartitionColum n in (1000,
10000))

But if you change the check constraints to

CONSTRAINT [check_Partition Column1] CHECK (PartitionColum n in (1,100,1000))
CONSTRAINT [check_Partition Column2] CHECK (PartitionColum n 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_MyPartionTes t1] PRIMARY KEY CLUSTERED
(
[RowId],
[PartitionColumn]
) ON [PRIMARY] ,
CONSTRAINT [check_Partition Column1] CHECK (PartitionColum n in (1,100))
) ON [PRIMARY]
GO

CREATE TABLE [MyPartionTest2] (
[RowId] [int] NOT NULL ,
[PartitionColumn] int NOT NULL ,
[Data] [varchar] (20) NULL ,
CONSTRAINT [PK_MyPartionTes t2] PRIMARY KEY CLUSTERED
(
[RowId],
[PartitionColumn]
) ON [PRIMARY] ,
CONSTRAINT [check_Partition Column2] CHECK (PartitionColum n 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,'Dat a 1-100')

--
-Dick Christoph
dchristo@mn.rr. com
612-724-9282
"sameer_deshpan de" <sameer_deshpan de@hotmail.com> wrote in message
news:1146733086 .482632.96930@u 72g2000cwu.goog legroups.com...[color=blue]
> 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
>[/color]

[DickChristoph]

Re: Partition function - Unknow range column

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
[color=blue]
> CONSTRAINT [check_Partition Column1] CHECK (PartitionColum n in (1,2))
> CONSTRAINT [check_Partition Column2] CHECK (PartitionColum n in (3, 4))[/color]
-Dick

--
"DickChrist oph" <dchristo99@yah oo.com> wrote in message
news:fFk6g.32$E C.22@tornado.rd c-kc.rr.com...[color=blue]
> Hi Sameer,
>
> The SQL Script below works to create a partioned updateable view
>
> It Uses these check constraints
> CONSTRAINT [check_Partition Column1] CHECK (PartitionColum n in (1,100))
> CONSTRAINT [check_Partition Column2] CHECK (PartitionColum n in (1000,
> 10000))
>
> But if you change the check constraints to
>
> CONSTRAINT [check_Partition Column1] CHECK (PartitionColum n in
> (1,100,1000))
> CONSTRAINT [check_Partition Column2] CHECK (PartitionColum n 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_MyPartionTes t1] PRIMARY KEY CLUSTERED
> (
> [RowId],
> [PartitionColumn]
> ) ON [PRIMARY] ,
> CONSTRAINT [check_Partition Column1] CHECK (PartitionColum n in (1,100))
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [MyPartionTest2] (
> [RowId] [int] NOT NULL ,
> [PartitionColumn] int NOT NULL ,
> [Data] [varchar] (20) NULL ,
> CONSTRAINT [PK_MyPartionTes t2] PRIMARY KEY CLUSTERED
> (
> [RowId],
> [PartitionColumn]
> ) ON [PRIMARY] ,
> CONSTRAINT [check_Partition Column2] CHECK (PartitionColum n 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,'Dat a 1-100')
>
> --
> -Dick Christoph
> dchristo@mn.rr. com
> 612-724-9282
> "sameer_deshpan de" <sameer_deshpan de@hotmail.com> wrote in message
> news:1146733086 .482632.96930@u 72g2000cwu.goog legroups.com...[color=green]
>> 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
>>[/color]
>
>[/color]

[sameer_deshpande]

Re: Partition function - Unknow range column

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

[DickChristoph]

Re: Partition function - Unknow range column

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[color=blue]
> CONSTRAINT [check_Partition Column0] CHECK (PartitionColum n = 0)[/color]

Table1[color=blue]
> CONSTRAINT [check_Partition Column1] CHECK (PartitionColum n = 1)[/color]

Table2[color=blue]
> CONSTRAINT [check_Partition Column2] CHECK (PartitionColum n = 2)[/color]

Table3[color=blue]
> CONSTRAINT [check_Partition Column3] CHECK (PartitionColum n = 3)[/color]

Table4[color=blue]
> CONSTRAINT [check_Partition Column4] CHECK (PartitionColum n = 4)[/color]

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_deshpan de" <sameer_deshpan de@hotmail.com> wrote in message
news:1146742117 .996286.179820@ v46g2000cwv.goo glegroups.com.. .[color=blue]
> 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
>[/color]

[sameer_deshpande]

Re: Partition function - Unknow range column

Not that easy to change design. The product support 6 diff RDBMS, so
the java code has to be consistent accross all the databases. Wont able
to affort change in the SQL statements depending on RDBMS - hard fact
:-(