Partitioning

Hello

I have a table containing 100,000 record for each year, and every year a new 100,000 record are inserted,
I need to know how to make partion this table by year

I need to know the syntax

Thank you

Any Ideas??

|||

Hi JRICE,

what do you mean by "make partion this table"? A simple "order by year" will sort your table based on the "year" column, but i'm not sure if it is what you want.

Also, could you please elaborate more information on the structure of your table? thanks

|||

Im talking about horizantal partitioning and i need some help in the syntax

Thank you

|||

100k records per year is really not big of a number that requires partitioning. SQL Server 2005 has some advanced options for partitioning and gives move flexibility. In 2000 its more of a simulated partitioning. You'd have to have a view UNIONing the partitions. Basically its a bit painful. Check this white paper on partitioning by Kimberley Trip:http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm

|||

Hi JRICE,

Which verison of sql server are you using? Based on my understanding, only enterprise edition support partition. If you have got one, you can try the following example:

CREATE PARTITION FUNCTION myRangePF1 (int)AS RANGE LEFT FOR VALUES (1, 100, 1000) ;GOCREATE PARTITION SCHEME myRangePS1AS PARTITION myRangePF1TO (test1fg, test2fg, test3fg, test4fg) ;GOALTER TABLE PartitionTable (col1 int, col2 char(10))ON myRangePS1 (col1) ;GO

This is one code example from msdn document, and it should work (i've only got a standard edition installed so i cannot test it on my side. sorry)

Hope my suggestion helps