MS SQL Server Clustering - SQL server 2000 fragmentation

Author

SQL server 2000 fragmentation

Nada Sherief

2005-11-08, 4:09 pm

hello
i want someone to help me in solving a problem in sql server 2000
i have this simple Entity relationship diagram drawn in the attached file with its realtionships

considering that i have two instances of SQL server on my PC one of them has the attached ERD on it
and i want to perform fragmentation to the tables as follows:

1- Horizontal fragmentation:

a-I want to divide the table PROJ into 2 relations. Subrelation PROJ1 contains information about projects whose budgets are less than $200,000, whereas PROJ2 stores information about projects with20larger budgets.

b- Vertical fragmentation

as an example take the same relation PROJ and divide it vertically into two subrelations PRO1 and PRO2. PRO1 contains only the information about project budgets, whereas PRO2 contains the proje
ct names and locations.
It is impoprtant to notice that the primary key to the relation (PNO) is included in both fragments.

c- derived horizontal fragmentation:

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.
there is a relationship between EMP and PAY and since the table PAY will be horizontally fragmented so the other table EMP should be derived horizontally fragmented.

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.

John Bell

2005-11-08, 4:09 pm

Hi

I think you are talking about partitioning rather than fragmentation?
Attachment are not always viewable from certain newsreaders or ISPs,
therefore it may have been better to include the DDL code See
http://www.aspfaq.com/etiquette.asp?id=5006

To horizontally partition you need to define a where clause. You can do this
in a view if you don't want the overhead of maintaining two tables. You will
need to add the correct column names:

CREATE VIEW PROJ1 AS
SELECT PNO, Budget, col1, col2, col3,...
FROM PROJ
WHERE budget < 200000

CREATE VIEW PROJ2 AS
SELECT PNO, Budget, col1, col2, col3,...
FROM PROJ
WHERE budget >= 200000

To partition vertically you only need to select the columns that you require:

CREATE VIEW PRO1 AS
SELECT PNO, Budget, col1, col2, col3...
FROM PROJ

CREATE VIEW PRO2 AS
SELECT PNO, col4, col5...
FROM PROJ

The rest I think you should be able to work out from that.

John

"Nada Sherief" wrote:

>
> hello
> i want someone to help me in solving a problem in sql server 2000
> i have this simple Entity relationship diagram drawn in the attached file with its realtionships
>
> considering that i have two instances of SQL server on my PC one of them has the attached ERD on it
> and i want to perform fragmentation to the tables as follows:
>
> 1- Horizontal fragmentation:
>
> a-I want to divide the table PROJ into 2 relations. Subrelation PROJ1 contains information about projects whose budgets are less than $200,000, whereas PROJ2 stores information about projects with larger budgets.
>
> b- Vertical fragmentation
>
> as an example take the same relation PROJ and divide it vertically into two subrelations PRO1 and PRO2. PRO1 contains only the information about project budgets, whereas PRO2 contains the project names and locations.
> It is impoprtant to notice that the primary key to the relation (PNO) is included in both fragments.
>
> c- derived horizontal fragmentation:
>
> 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.
> there is a relationship between EMP and PAY and since the table PAY will be horizontally fragmented so the other table EMP should be derived horizontally fragmented.
>
>
> 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

2005-11-08, 4:09 pm

Strategies for Partitioning Relational Data Warehouses in Microsoft SQL
Server
http://www.microsoft.com/technet/pr.../2005/spdw.mspx
TechNet Webcast: SQL Server 2005 Series (Part 6 of 10): Managing Large
Databases Using Partitioning (Level 200)
http://msevents.microsoft.com/cui/W...6&Culture=en-US

"Nada Sherief" <nadasherief@hotmail.com> wrote in message
news:7c7276cf18668c7
b069aa13a3b4@news.microsoft.com...

hello
i want someone to help me in solving a problem in sql server 2000
i have this simple Entity relationship diagram drawn in the attached file
with its realtionships

considering that i have two instances of SQL server on my PC one of them has
the attached ERD on it
and i want to perform fragmentation to the tables as follows:

1- Horizontal fragmentation:

a-I want to divide the table PROJ into 2 relations. Subrelation PROJ1
contains information about projects whose budgets are less than $200,000,
whereas PROJ2 stores information about projects with larger budgets.

b- Vertical fragmentation

as an example take the same relation PROJ and divide it vertically into two
subrelations PRO1 and PRO2. PRO1 contains only the information about project
budgets, whereas PRO2 contains the project names and locations.
It is impoprtant to notice that the primary key to the relation (PNO) is
included in both fragments.

c- derived horizontal fragmentation:

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.
there is a relationship between EMP and PAY and since the table PAY will be
horizontally fragmented so the other table EMP should be derived
horizontally fragmented.

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.

Sponsored Links