hello

i want someone to help me in solving a problem in sql server 2000

considering that i have a table named PAY(TITLE, SAL) where TITLE is the
primary key of this table
also this table is related to another one named EMP, where the other table
has a foreign
key to this table.

table EMP(ENO, ENAME, TITLE) where ENO is the primary key and it is related
to another table that contains a foreign key to this table.

i want to perform Derived horizontal fragmentation (partitioning) on SQL
server 2000 for EMP:

first: I want to divide the table PAY into 2 relations. Subrelation PAY1
contains information about job titles whose salaries are less than or equal
to $300,000, whereas PAY2 stores information job titles with larger salaries.

second: I want to divide the table EMP into 2 relations. Subrelation EMP1
contains information about employees whose salaries are less than or equal
to 300,000, whereas EMP2 stores information about projects with larger salaries.

please try to send me the code (with comments) and a detailed step-by-step
of how to run this code so that i have these tables fragmented on the two
servers.

or if they don't need code, i hope you send me a step-by-step support of
how to do all these fragmentations on SQL server 2000.

Thanx al

My previous answer to your latest question will solve the problem
--
Regards
R.D
--Knowledge gets doubled when shared


"Nada Sherief" wrote:

>
> hello
>
> i want someone to help me in solving a problem in sql server 2000
>
> considering that i have a table named PAY(TITLE, SAL) where TITLE is the
> primary key of this table
> also this table is related to another one named EMP, where the other table
> has a foreign
> key to this table.
>
> table EMP(ENO, ENAME, TITLE) where ENO is the primary key and it is related
> to another table that contains a foreign key to this table.
>
> i want to perform Derived horizontal fragmentation (partitioning) on SQL
> server 2000 for EMP:
>
> first: I want to divide the table PAY into 2 relations. Subrelation PAY1
> contains information about job titles whose salaries are less than or equal
> to $300,000, whereas PAY2 stores information job titles with larger salaries.
>
> second: I want to divide the table EMP into 2 relations. Subrelation EMP1
> contains information about employees whose salaries are less than or equal
> to 300,000, whereas EMP2 stores information about projects with larger salaries.
>
> please try to send me the code (with comments) and a detailed step-by-step
> of how to run this code so that i have these tables fragmented on the two
> servers.
>
> or if they don't need code, i hope you send me a step-by-step support of
> how to do all these fragmentations on SQL server 2000.
>
> Thanx all
>
>
>

If PAY is to be partitoned based on Salary and an employees's salary
increases from $290,000 to $310,000, would their related salary information
need to be deleted from PAY1 and re-inserted into PAY2? Generally speaking,
tables are partitioned because there is large volume of rows, like >
1,000,000, and the data is split between tables based on something like
EntryDate, RegionID or some other ID that is static and indexed.

Is there a one to many relationship between EMP and PAY?

Perhaps you are just wanting to subset results for reporting purposes?


"Nada Sherief" <nadasherief@hotmail.com> wrote in message
news:7c7276cf31f68c7b21d8f03cf12@news.microsoft.co m...
>
> hello
> i want someone to help me in solving a problem in sql server 2000
>
> considering that i have a table named PAY(TITLE, SAL) where TITLE is the
> primary key of this table
> also this table is related to another one named EMP, where the other table
> has a foreign key to this table.
>
> table EMP(ENO, ENAME, TITLE) where ENO is the primary key and it is
> related to another table that contains a foreign key to this table.
>
> i want to perform Derived horizontal fragmentation (partitioning) on SQL
> server 2000 for EMP:
>
> first: I want to divide the table PAY into 2 relations. Subrelation PAY1
> contains information about job titles whose salaries are less than or
> equal to $300,000, whereas PAY2 stores information job titles with larger
> salaries.
>
> second: I want to divide the table EMP into 2 relations. Subrelation EMP1
> contains information about employees whose salaries are less than or equal
> to 300,000, whereas EMP2 stores information about projects with larger
> salaries.
>
> please try to send me the code (with comments) and a detailed step-by-step
> of how to run this code so that i have these tables fragmented on the two
> servers.
>
> or if they don't need code, i hope you send me a step-by-step support of
> how to do all these fragmentations on SQL server 2000.
>
> Thanx all
>
>

Hello JT,

the answer for the first question is consider that i have some data in the
tables and i don't care if they are going to be changed or not.
and yes there is a one to many relationship between EMP and PAY
so

first: I want to divide the table PAY into 2 relations. Subrelation
PAY1 contains information about job titles whose salaries are less
than or equal to $300,000, whereas PAY2 stores information job titles
with larger salaries.

second: I want to divide the table EMP into 2 relations. Subrelation
EMP1 contains information about employees whose salaries are less
than or equal to 300,000, whereas EMP2 stores information about
projects with larger salaries.

please help me find a solution to this problem

Thanx



If PAY is to be partitoned based on Salary and an employees's salary
> increases from $290,000 to $310,000, would their related salary
> information need to be deleted from PAY1 and re-inserted into PAY2?
> Generally speaking, tables are partitioned because there is large
> volume of rows, like > 1,000,000, andda the ta is split between tables
> based on something like EntryDate, RegionID or some other ID that is
> static and indexed.
>
> Is there a one to many relationship between EMP and PAY?
>
> Perhaps you are just wanting to subset results for reporting purposes?
>
> "Nada Sherief" <nadasherief@hotmail.com> wrote in message
> news:7c7276cf31f68c7b21d8f03cf12@news.microsoft.co m...
>
>> hello
>> i want someone to help me in solving a problem in sql server 2000
>> considering that i have a table named PAY(TITLE, SAL) where TITLE is
>> the
>> primary key of this table
>> also this table is related to another one named EMP, where the other
>> table
>> has a foreign key to this table.
>> table EMP(ENO, ENAME, TITLE) where ENO is the primary key and it is
>> related to another table that contains a foreign key to this table.
>>
>> i want to perform Derived horizontal fragmentation (partitioning) on
>> SQL server 2000 for EMP:
>>
>> first: I want to divide the table PAY into 2 relations. Subrelation
>> PAY1 contains information about job titles whose salaries are less
>> than or equal to $300,000, whereas PAY2 stores information job titles
>> with larger salaries.
>>
>> second: I want to divide the table EMP into 2 relations. Subrelation
>> EMP1 contains information about employees whose salaries are less
>> than or equal to 300,000, whereas EMP2 stores information about
>> projects with larger salaries.
>>
>> please try to send me the code (with comments) and a detailed
>> step-by-step of how to run this code so that i have these tables
>> fragmented on the two servers.
>>
>> or if they don't need code, i hope you send me a step-by-step support
>> of how to do all these fragmentations on SQL server 2000.
>>
>> Thanx all
>>

I think that reading up on Views will solve your problem.

Related partitioned tables are typically joined (vertically) or unionized
(horizontally) by implementing views.

For example:

CREATE view InvoiceHistory
as
Select * from INVOICES_2004 UNION ALL
Select * from INVOICES_2003 UNION ALL
Select * from INVOICES_2002
GO

This would allow querying invoices across all years like so:

select * from InvoiceHistory

Here are a couple of good links describing this in more detail:
http://msdn.microsoft.com/library/de...itionsInDW.htm
http://www.microsoft.com/technet/pro...2005/spdw.mspx

However, notice that these articles revolve around data warehousing
concepts. Generally, speaking tables are not partitioned, unless you are
wanting to segment a large amount of data for performance reasons. It sounds
like you are wanting to partition related data into seperate tables for what
you think are logical reasons, but this de-normalizes your database model
and provides no benefit. All it would do is make your queries more complex.
http://en.wikipedia.org/wiki/Database_normalization

If you are wanting to retrict update to only specific columns in a table,
then consider implementing a view that only returns those updatble columns
and give the application access to that rather than the base table.
http://msdn.microsoft.com/library/de...urity_5whf.asp


"Nada Sherief" <nadasherief@hotmail.com> wrote in message
news:7c7276cf35f88c7b2bed08573c2@news.microsoft.co m...
> Hello JT,
>
> the answer for the first question is consider that i have some data in the
> tables and i don't care if they are going to be changed or not.
> and yes there is a one to many relationship between EMP and PAY
> so
>
> first: I want to divide the table PAY into 2 relations. Subrelation
> PAY1 contains information about job titles whose salaries are less
> than or equal to $300,000, whereas PAY2 stores information job titles
> with larger salaries.
>
> second: I want to divide the table EMP into 2 relations. Subrelation
> EMP1 contains information about employees whose salaries are less
> than or equal to 300,000, whereas EMP2 stores information about
> projects with larger salaries.
>
> please help me find a solution to this problem
>
> Thanx
>
>
>
> If PAY is to be partitoned based on Salary and an employees's salary
>> increases from $290,000 to $310,000, would their related salary
>> information need to be deleted from PAY1 and re-inserted into PAY2?
>> Generally speaking, tables are partitioned because there is large
>> volume of rows, like > 1,000,000, andda the ta is split between tables
>> based on something like EntryDate, RegionID or some other ID that is
>> static and indexed.
>>
>> Is there a one to many relationship between EMP and PAY?
>>
>> Perhaps you are just wanting to subset results for reporting purposes?
>>
>> "Nada Sherief" <nadasherief@hotmail.com> wrote in message
>> news:7c7276cf31f68c7b21d8f03cf12@news.microsoft.co m...
>>
>>> hello
>>> i want someone to help me in solving a problem in sql server 2000
>>> considering that i have a table named PAY(TITLE, SAL) where TITLE is
>>> the
>>> primary key of this table
>>> also this table is related to another one named EMP, where the other
>>> table
>>> has a foreign key to this table.
>>> table EMP(ENO, ENAME, TITLE) where ENO is the primary key and it is
>>> related to another table that contains a foreign key to this table.
>>>
>>> i want to perform Derived horizontal fragmentation (partitioning) on
>>> SQL server 2000 for EMP:
>>>
>>> first: I want to divide the table PAY into 2 relations. Subrelation
>>> PAY1 contains information about job titles whose salaries are less
>>> than or equal to $300,000, whereas PAY2 stores information job titles
>>> with larger salaries.
>>>
>>> second: I want to divide the table EMP into 2 relations. Subrelation
>>> EMP1 contains information about employees whose salaries are less
>>> than or equal to 300,000, whereas EMP2 stores information about
>>> projects with larger salaries.
>>>
>>> please try to send me the code (with comments) and a detailed
>>> step-by-step of how to run this code so that i have these tables
>>> fragmented on the two servers.
>>>
>>> or if they don't need code, i hope you send me a step-by-step support
>>> of how to do all these fragmentations on SQL server 2000.
>>>
>>> Thanx all
>>>
>
>