SQL server 2000 fragmentation

5 Nov 2005 7:47 PM

Nada Sherief

[Message not available]

6 Nov 2005 3:24 PM

John Bell

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

Show quote

"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