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]

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
dc******@mn.rr.com
612-724-9282
"sameer_deshpande" <sa**************@hotmail.com> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.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

[DickChristoph]

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" <dc********@yahoo.com> wrote in message
news:fF************@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
dc******@mn.rr.com
612-724-9282
"sameer_deshpande" <sa**************@hotmail.com> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.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

[sameer_deshpande]

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]

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" <sa**************@hotmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.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

[sameer_deshpande]

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
:-(