Partition tables in SQL Server 2005

I'm studing the partitions of SQL2005 for future implementation and I have some questions to make:

* Can I associate more then on Partitions Functions to a table
With this feature I want to know if I can have partitions combined with 2 or more table columns.

CREATE TABLE [dbo].[OrdersRange]
(
    [OrderID]        [int]                NOT NULL,
    [OrderType]    [int]                NOT NULL,
    [OrderValue]    [money]        NOT NULL,
    [OrderDate]    [datetime]        NULL
)
ON OrderDateSchme(OrderDate)
ON OrderTypeRange(OrderType)
GO

This return error. Can I do this any other way

* There is any way to create a rule to create ranges
My client want to have dayllie partitions. The is any way to say that to the system insted of create anything in my program that every day create a SPLIT in the Partitions Functions

Hope you can ask this small questions about partitions in SQL2005

tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal

Answer this question

Partition tables in SQL Server 2005

MC8005

1. You can only partition by one function. You can however create a computed column that combines two or more columns and partition based on the that. This is one way to workaround the limitation

2. There is no automatic way to create new partitions. You have to manage that yourself. You can use a SQLAgent job for instance to create the daily partitions or as part of a daily job that uploads data into these partitions.
JCBS
If you want to partition by more than one column, you'll need to create a computed column (which can be the result of a user-defined function) and then partition over this.

In our experience, this covers most of the basic partitioning cases.

Conor Cunningham

SQL Server Query Optimization Development Lead
Partition tables in SQL Server 2005

Answer this question