Table partitioning using 2 columns

Question

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 1

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.

Answer 2

I found this was an easier solution

select ROW_NUMBER() over (partition by CHECKSUM(value,ID) order by SortOrder) as Row From your_table