Table partitioning using 2 columns

sql-server-2008 partitioning
Rafael Colucci picture Rafael Colucci · Jan 12, 2011 · Viewed 42.5k times · Source

Is it possible to partition a table using 2 columns instead of only 1 for the partition function?

Consider a table with 3 columns 

    ID (int, primary key, 
    Date (datetime), 
    Num (int)

I want to partition this table by 2 columns: Date and Num.

This is what I do to partition a table using 1 column (date):

create PARTITION FUNCTION PFN_MonthRange (datetime)
AS
RANGE left FOR VALUES ('2009-11-30 23:59:59:997',
                       '2009-12-31 23:59:59:997',
                       '2010-01-31 23:59:59:997',
                       '2010-28-02 23:59:59:997',
                       '2010-03-31 23:59:59:997')
go

Answer

Joe Stefanelli picture Joe Stefanelli · Jan 12, 2011

Bad News: The partition function has to be defined on a single column.

Good News: That single column could be a persisted computed column that is a combination of the two columns you're trying to partition by.

Related questions

Undo Table Partitioning

I have a table 'X' and did the following CREATE PARTITION FUNCTION PF1(INT) AS RANGE LEFT FOR VALUES (1, 2, 3, 4) CREATE PARTITION SCHEME PS1 AS PARTITION PF1 ALL TO ([PRIMARY]) CREATE CLUSTERED INDEX CIDX_X ON X(col1) ON PS1(col1) …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More