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

score 53 · Accepted Answer · answered Jan 12 '11 at 16:40

53

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.

answered Jan 12 '11 at 16:40

Joe Stefanelli

132,803
19
237
235

6

I am not sure that your "Bad news" is correct. I am having no problem specifying a partition over multiple columns, e.g. `SELECT ROW_NUMBER() OVER (PARTITION BY a.c1, b.c2, a.c3 ORDER BY a.c1, b.c2, a.c3, a.c4) FROM ...`, and http://msdn.microsoft.com/en-us/library/ms189461%28v=sql.105%29.aspx clearly shows that you can specify multiple value expressions for the partition. Am I missing something? – Nathan Jul 18 '13 at 18:34
13

@Nathan: The original question was about [table partitioning](http://msdn.microsoft.com/en-us/library/ms190787.aspx), not about window functions. – Joe Stefanelli Jul 18 '13 at 21:22
2

ah! Looking at the other upvoted answer, I guess I'm not the only one that made that mistake! – Nathan Jul 18 '13 at 21:26

score 6 · Answer 2 · edited Jan 16 '12 at 10:47

6

I found this was an easier solution

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

edited Jan 16 '12 at 10:47

eckes

64,417
29
168
201

answered Jan 16 '12 at 10:42

Jonathan

189
2
3

11

Checksum can produce the same output value from different input values, (basically a hash collision). Therefore this method is not 100% reliable. So if your partitioning absolutely must be correct, don't use this method. If you can tolerate a rare incorrect partition, then this is fine. – Nathan Jul 18 '13 at 17:31
5

I think the question is related to table partitioning and not windows functions. – Sourabh Jain Sep 03 '19 at 06:10

score 4 · Answer 3 · answered Jan 12 '11 at 16:42

Natively, no you can not partition by two columns in SQL Server.

There are a few things you could do, have a lookup table that you use to extract which arbitary integer (partition) each value is within, but you only have 1000 partitions maximum, so they are going to start occupying the same space. The computed column approach suffers this same problem, you have a 1k partition limit, chances are you will blow it.

I would probably just stick to a date partition, and range right on the 1st of the month, instead of ranging left on the last part of the month.

What do you intend to gain from the second partition value?

Having two partition columns could possibly be used for Multi-tenant *and* Date-retention partitioning.. but n-axis partitions are hard to deal with simulated and there is no true support, as evidenced by the original restriction. (Also, 7 years later the limit is 15k partitions :D) — user2864740, Oct 26 '18 at 09:11

Table partitioning using 2 columns

3 Answers3

Linked

Related