使用 2 列进行表分区
是否可以使用 2 列而不是仅 1 列的分区函数来对表进行分区？
考虑一个包含 3 列的表，
ID (int, primary key,
Date (datetime),
Num (int)
我想将该表按 2 列分区：Date 和 Num。
这就是我使用 1 列（日期）对表进行分区的方法：
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
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
坏消息：分区函数必须在单个列上定义。
好消息：该单列可以是持久计算列 这是您尝试分区的两列的组合。
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.
我发现这是一个更简单的解决方案
I found this was an easier solution
本质上，您不能在 SQL Server 中按两列进行分区。
您可以做一些事情，使用一个查找表来提取每个值所在的任意整数（分区），但最多只有 1000 个分区，因此它们将开始占用相同的空间。计算列方法也遇到同样的问题，您有 1k 分区限制，很可能您会超出它。
我可能会坚持使用日期分区，并在该月的第一天向右排列，而不是在该月的最后一部分向左排列。
您打算从第二个分区值中获得什么？
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?