473,747 Members | 2,822 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Partition function - Unknow range column

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

May 4 '06 #1
5 5949
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
dc******@mn.rr. com
612-724-9282
"sameer_deshpan de" <sa************ **@hotmail.com> wrote in message
news:11******** *************@u 72g2000cwu.goog legroups.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

May 4 '06 #2
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_Partition Column1] CHECK (PartitionColum n in (1,2))
CONSTRAINT [check_Partition Column2] CHECK (PartitionColum n in (3, 4)) -Dick

--
"DickChrist oph" <dc********@yah oo.com> wrote in message
news:fF******** ****@tornado.rd c-kc.rr.com... 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
dc******@mn.rr. com
612-724-9282
"sameer_deshpan de" <sa************ **@hotmail.com> wrote in message
news:11******** *************@u 72g2000cwu.goog legroups.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


May 4 '06 #3
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

May 4 '06 #4
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_Partition Column0] CHECK (PartitionColum n = 0)
Table1 CONSTRAINT [check_Partition Column1] CHECK (PartitionColum n = 1)
Table2 CONSTRAINT [check_Partition Column2] CHECK (PartitionColum n = 2)
Table3 CONSTRAINT [check_Partition Column3] CHECK (PartitionColum n = 3)
Table4 CONSTRAINT [check_Partition Column4] CHECK (PartitionColum n = 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_deshpan de" <sa************ **@hotmail.com> wrote in message
news:11******** **************@ v46g2000cwv.goo glegroups.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

May 4 '06 #5
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
:-(

May 4 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

41
3552
by: Xah Lee | last post by:
here's another interesting algorithmic exercise, again from part of a larger program in the previous series. Here's the original Perl documentation: =pod merge($pairings) takes a list of pairs, each pair indicates the sameness of the two indexes. Returns a partitioned list of same indexes.
10
2278
by: Xah Lee | last post by:
another functional exercise with lists. Here's the perl documentation. I'll post a perl and the translated python version in 48 hours. =pod parti(aList, equalFunc) given a list aList of n elements, we want to return a list that is a
4
6083
by: eavery | last post by:
I can't seem to find a way to do the following: create table part_table ( col1 int, col2 datetime ) on psX (datename(week,col2)) I want to partition based on the week number of a date field. So if I enter in data like the following in my part_table:
3
4349
by: nbajrach | last post by:
how to partition on a table if table already exits without any partition. This is what i tried but gave me error SQL> alter table sip add partition by range(si_id) 2 partition p1_si values less than (50001), 3 partition p2_si values less than (100001),
8
17331
ADezii
by: ADezii | last post by:
The inspiration for this Tip was an initial reply by one of our resident Experts to a specific Post. In this Post, the User wanted to know how he could calculate the total number of test results (Satisfactory/Unsatisfactory) for specific age group intervals such as: 20-24, 25-29, etc. The use of the Partition Function was suggested, I followed up with a reply of my own, and it seemed as though some interest was generated in this little know...
0
12978
by: Vinod Sadanandan | last post by:
Table Partition Performance analysis ============================================ Collection of Statistics for Cost-Based Optimization/DBMS_STATS vs. ANALYZE The cost-based approach relies on statistics and if the cost-based Approach is used , then statistics should be gernerated for all tables, clusters, and all types of indexes accessed by SQL statements. If the size and data distribution of your tables change frequently, then...
0
3786
debasisdas
by: debasisdas | last post by:
SAMPLE CODE TO CREATE SUB PARTITIONS ======================================= RANGE-HASH-9i ------------------------- CREATE TABLE SUBPART ( ID NUMBER(10) PRIMARY KEY, NAME VARCHAR2(20) )
0
3425
debasisdas
by: debasisdas | last post by:
USING PARTITION =================== PARTITION BY RANGE-as per Oracle 8 -------------------------------------- CREATE TABLE RANGEPART ( ID NUMBER(2) PRIMARY KEY, NAME VARCHAR2(20) )
3
3634
by: Justin | last post by:
What is the syntax to overlap partitions? Lets assume I want a year split amount 4 months (3 partitions for a year) and later adding an additional set of partitions for every 6 months. Plus giving us the option to detach old partitions. For example: CREATE TABLE orders(id INT, shipdate DATE, …) PARTITION BY RANGE(shipdate) ( PARTITION m12y05 STARTING MINVALUE,
0
9522
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9354
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9307
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9223
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6067
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4588
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3296
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2771
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.