Table/Index Partitioning in SQL Server 2005

Another question commonly ask is how can a DBA define data partitions in SQL Server (similar to what can be done in Oracle using PARTITION BY RANGE).

Prior to SQL Server 2005, DBAs who want to achieve data partitioning would need to create individual tables/indexes, and then impose a view over these partitions(using UNION). When a query is executed, the query optimizer has to validate and determine plans for each of the partition (as each of the partitions could have different indexes built). In SQL Server 2005, each data partition would have the same index (by definition), which greatly simplifies and speed-up the query optimization step.

In addition, SQL Server 2005 also provides a partitioning scheme, which specifies how the system maps a data record to one or more filegroups. To define the partitions in SQL Server 2005, you can check out the CREATE PARTITION FUNCTION function, and CREATE PARTITION SCHEME clauses.

Once the partition function and scheme is defined, you can create your tables using the USUAL CREATE TABLE SQL command, together with the On keyword which specifies the partitioning scheme to use.

CREATE TABLE [dbo].[MyTable]
(
...
)
ON [MyPartitioningScheme] (partitioningKey)


You can find out more here

http://msdn.microsoft.com/sql/learning/Perf/
default.aspx?pull=/library/en-us/dnsql90/html/sql2k5partition.asp