derived horizontal partitioning on SQL server 2000

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
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[vbcol=seagreen]
> 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...
|||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
>
sql

derived horizontal partitioning on SQL server 2000

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
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[vbcol=seagreen]
> 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...
|||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
>

derived horizontal partitioning on SQL server 2000

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 salar
ies.
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 allMy 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 relate
d
> 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 equa
l
> to $300,000, whereas PAY2 stores information job titles with larger salari
es.
> 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 sal
aries.
> 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.com...
> 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.com...
>|||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/d...nDW.
htm
http://www.microsoft.com/technet/pr.../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/d...>
ity_5whf.asp
"Nada Sherief" <nadasherief@.hotmail.com> wrote in message
news:7c7276cf35f88c7b2bed08573c2@.news.microsoft.com...
> 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
>

derived horizontal partitioning on SQL server 2000

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
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[vbcol=seagreen]
> 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...
|||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
>

derived horizontal partitioning on SQL server 2000

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 salar
ies.
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 allMy 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 relate
d
> 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 equa
l
> to $300,000, whereas PAY2 stores information job titles with larger salari
es.
> 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 sal
aries.
> 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.com...
> 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[vbcol=seagreen]
> 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.com...
>|||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/d...nDW.
htm
http://www.microsoft.com/technet/pr.../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/d...>
ity_5whf.asp
"Nada Sherief" <nadasherief@.hotmail.com> wrote in message
news:7c7276cf35f88c7b2bed08573c2@.news.microsoft.com...
> 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
>

deploying reports from vs2005 issue

I'm trying to deploy reports to development server that is running
mssql 2000 and a named instance of mssql 2005 but when I deploy I'm
running into authentication issues. From some articles I'm finding
that this can occur when using SSRS in mssql2000. Do I need to modify
the deployment path in visual studio to some how point to the named
mssql 2005 database, if so how? Also where do I find the
configuration in vs2005 to not overwrite the datasources when a report
is deployed?
Thanks,
JeffWhen you setup for deployment you give it the web address. If your report
server is working that is all you should need to do. For instance it would
look like this for TargetServerURL: http://yourserver/ReportServer
Now, to be able to deploy you need to have the rights to do so. This is
Reporting Services rights. It has nothing to do with SQL Server DB rights.
Can you run reports? First make sure of that.
If your domain account is in the local administrators group on the server
then you will automatically have all the rights you need. Again, this is
around your windows account, not your SQL Server access rights.
Also, the default in vs2005 is to NOT overwrite a datasource. Right mouse
click on the project, properties. This is where you set the targetserverurl
and also the overwritedatasources (which you want to be false).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeffa" <Jeff.Arlt@.gmail.com> wrote in message
news:1183479523.437839.111440@.j4g2000prf.googlegroups.com...
> I'm trying to deploy reports to development server that is running
> mssql 2000 and a named instance of mssql 2005 but when I deploy I'm
> running into authentication issues. From some articles I'm finding
> that this can occur when using SSRS in mssql2000. Do I need to modify
> the deployment path in visual studio to some how point to the named
> mssql 2005 database, if so how? Also where do I find the
> configuration in vs2005 to not overwrite the datasources when a report
> is deployed?
> Thanks,
> Jeff
>

Deploy to named instance

Is it possible to deploy to a named instance from the default instance on the same machine? I keep gettin 'unable to connect' error.

Thanks.

SQL Server 2005 sp 2/Vista

> deploy to a named instance from the default instance on the same machine?

What does it mean "deploy from instance" ? Normally the project is deployed from BI Dev Studio. There in the Deployment Options, you specify the named instance as a server to deploy to, and it will work.

|||

I get the following error message: The project could not be deployed to the '...' server because of the following connectivity problems" '....' instance was not found on '..' server

I know the instance is there because I can open it in sql server mgmt studio. I can also use the named instance as a data source.

|||

Further information: Microsoft doesn't like instance names to include the '_' character, as in SQL_TWO.

Getting closer....just one Mdx script error

deploy SQL Server Express

Problem: (asp.net 2.0)

I x-copy from my development server a database named C:\site_test\site.mdf to C:\site. When I attach the database with Server Management Studio Express, it list the name as: "C:\site_test\site.mdf" eventhough on the live server the database is in the C:\site directory and my connection string in my web.config is "C:\site\site.mdf".

But everything works until a week later when I need to make a field change to a table on the live site: I'm forced to re-attach the database, but this time it list the database as:C:\site\site.mdf". When I re-attach I get a NT AUTHORITY\SERVICE does not have permission. So I give NT AUTHORITY\SERVICE permission on the directory and files and ownership with Server Management Stuidio Express, but I still get the error.

What am I'm doing wrong? Thanks for the help

--Dietrich

Hi Dietrich,

If you have attached the .mdf file to database manually, please check if you have set the connection string accordingly.

It has to be "Data Source=ServerName\Instancename; Initial Catelog=DatabaseName"