MS SQL Data Warehousing - SQL server 2000 fragmentation

Author

SQL server 2000 fragmentation

Nada Sherief

2005-11-08, 9:23 am

hi,

i want someone to help me in solving a problem in sql server 2000
considering that i have a table named PROJ(JNO,JNAME,BUDGE
T,LOC) where JNO
is the primary key of this table
also this table is related to another one, where the other table has a foreign
key to this table.

i want to perform horizontal fragmentation:

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.

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

Mike Epprecht $SQL MVP$

2005-11-08, 9:23 am

Hi

The term is Partitioning

Look at:
http://msdn.microsoft.com/library/d...k5partition.asp

SQL 2000 specific:
http://msdn.microsoft.com/library/d...des_06_17zr.asp

I never though a newsreader would support cross posting to so many
newsgroups, please post to a specific group in future.

--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Nada Sherief" <nadasherief@hotmail.com> wrote in message
news:7c7276cf30f38c7
b1ff62de1a46@news.microsoft.com...
>
> hi,
>
> i want someone to help me in solving a problem in sql server 2000
> considering that i have a table named PROJ(JNO,JNAME,BUDGE
T,LOC) where JNO
> is the primary key of this table
> also this table is related to another one, where the other table has a
> foreign key to this table.
>
> i want to perform horizontal fragmentation:
>
> 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.
>
>
> 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
>
>

Jéjé

2005-11-08, 9:23 am

look at the partitionned table feature of SQL 2000.

you have to create the same table on each server, link the servers , create
a view like :
select * from server1.database.dbo.table1
union all
select * from server2.database.dbo.table2

you have to add constraint on each table like "BUDGET > 200000" for table1
and "BUDGET<=200000" for table2

some articles:
http://msdn.microsoft.com/library/d..._ar_cs_5335.asp
http://msdn.microsoft.com/library/d...titionsindw.asp
(look at the check constraint)

good luck

"Nada Sherief" <nadasherief@hotmail.com> wrote in message
news:7c7276cf30f38c7
b1ff62de1a46@news.microsoft.com...
>
> hi,
>
> i want someone to help me in solving a problem in sql server 2000
> considering that i have a table named PROJ(JNO,JNAME,BUDGE
T,LOC) where JNO
> is the primary key of this table
> also this table is related to another one, where the other table has a
> foreign key to this table.
>
> i want to perform horizontal fragmentation:
>
> 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.
>
>
> 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
>
>

Sponsored Links