How do I Partition a table by date in SQL server 2000

Question

How do I Partition a table by date in SQL server 2000

Mal Content

Valued Member

Points: 72
More actions
September 20, 2007 at 4:01 pm

#153846

I have a table with a couple of million rows in it. Of this I need frequent access to about 70,000 records which are the most recent records.
Although there are many date fields in the table I am unable to create a clustered index on any of them. OTOH there are at least a half a dozen unclustered indexes (including compound indexes) so that certain applications can get at the subsets they need.
Unfortunately despite days of experimentation with different indexing schemes I am unable to make these queries run in an acceptable time.
I noticed that most queries limit themselves to records created in the last 30 thirty days so I was hoping I could have SQL server partition the data on disk into different table spaces like postgres or oracle does.
I can not find anything in the manuals on how to partition according to index values. Does anybody have any idea how this could be done?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

michaela SSCertifiable Points: 5649 More actions · Answer 1

Table partitioning is not a built-in functionality for sql 2000; however, this works good in sql 2005, but unfortunately it's not your case. You can use partitioned views when accessing your data if you're running enterprise version - see BOL for how to implement partitioned views in sql 2000.

You can also develop a customised solution for splitting a big table in many files for storage purposes but the application has to be modified accordingly in order to handle it - more of a headack.

Good luck

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

Shoot... that's not true in 2000... lookup "Creating a Partioned View" to see how to partition tables... :discuss:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

Sorry... my mistake... didn't read the reply well enough and this new system won't let me edit or delete my previous post...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)