SQLServer表分区 [英] SQLServer Table Partitioning
问题描述
嗨！
我有一个问题：
我已经有一个使用分区在美国划分数据的数据库
县，按州划分。
我可以使用两级分区吗？
我的意思是... 3077文件组和50个分区函数，它们可以解决这些问题，但我可以使用另一个函数对50个州进行分组吗？
谢谢！
Piero
Hi!
I have a question:
I already have a DB that uses partitions to divide data in US
Counties, partitioned by state.
Can I use TWO levels of partitioning?
I mean... 3077 filegroups and 50 partition functions that address
them, but can I use another function to group the 50 states?
Thanks!
Piero
推荐答案
Piero''Giops''Giorgi（gi ********** @ gmail.com）写道：
Piero ''Giops'' Giorgi (gi**********@gmail.com) writes:
我已经有一个使用分区在美国划分数据的数据库
我可以使用两级分区吗？
我的意思是...... 3077个文件组和50个分区函数可以解决
他们，但我可以使用另一个功能来分组50个州吗？
I already have a DB that uses partitions to divide data in US
Counties, partitioned by state.
Can I use TWO levels of partitioning?
I mean... 3077 filegroups and 50 partition functions that address
them, but can I use another function to group the 50 states?
我是否正确理解你已经拥有50个分区，现在你想要更多？
您希望每个分区的大小是多少？
我不确定按州划分是最好的策略。 Californina的分区
将远远大于阿拉斯加和罗德岛的分区
Island。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
在线预订SQL Server 2005
http://www.microsoft.com/technet/pro...ads/books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons /books.mspx
Do I understand it correctly that you already have 50 partitions, and
now you want even more? About what size do you expect per partition?
I''m not sure that partitioning by state is the best strategy. The partition
for Californina will be a lot bigger than the ones for Alaska and Rhode
Island.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
我是否正确理解您已有50个分区，并且
现在你想要更多？您希望每个分区的大小是多少？
我不确定按州划分是最好的策略。加利福尼亚的分区
将比阿拉斯加和罗德的分区大得多
Island。
Do I understand it correctly that you already have 50 partitions, and
now you want even more? About what size do you expect per partition?
I''m not sure that partitioning by state is the best strategy. The partition
for California will be a lot bigger than the ones for Alaska and Rhode
Island.
我知道，但按县分区会让数据库更容易维护。
我我必须按照这种方式工作，因为我正在处理犯罪记录，并且他们被县分开，有大量不同的档案，所以对于
我们很多人我每次得到更新时都必须清理表并重新加载整个县
。分区更容易...... :-)
表格大小可以是8000到300万条记录，具体取决于县内的
。
最好的方法是将表分区超过3077
文件组，因此存储数据将由COUNTY以这种方式进行：
CA_ALAMEDA
CA_ALPINE
CA_AMADOR
CA_BUTTE
CA_CALAVERAS
CA_COLUSA
CA_CONTRA_COSTA
CA_DEL_NORTE
CA_EL_DORADO
CA_FRESNO
CA_GLENN
CA_HUMBOLDT
CA_IMPERIAL
CA_INYO
将COUNTY作为分区参数。
但是，在尝试之前，我可以在一个分区中拥有3077个文件，然后放弃
所有州的东西吗？
谢谢！
Piero
I know that, but partitioning by county makes the DB a lot easier to
maintain.
I have to work that way because I''m dealing with criminal records, and
they are separated by county with a ton of different files, so for
many of them I have to clear the table and reload the whole county
every time I get an update. Easier on partitions... :-)
Table size can be anywhere from 8000 to 3 million records, depending
on the county.
The best way to do that would be having a table partitioned over 3077
filegroups, so storing the data will go by COUNTY in this way:
CA_ALAMEDA
CA_ALPINE
CA_AMADOR
CA_BUTTE
CA_CALAVERAS
CA_COLUSA
CA_CONTRA_COSTA
CA_DEL_NORTE
CA_EL_DORADO
CA_FRESNO
CA_GLENN
CA_HUMBOLDT
CA_IMPERIAL
CA_INYO
With the COUNTY as the partition Parameter.
But, before trying, can I have 3077 files in ONE partition, and drop
all the states stuff?
Thanks!
Piero
Piero''Giops''Giorgi（gi **********） @ gmail.com）写道：
Piero ''Giops'' Giorgi (gi**********@gmail.com) writes:
我知道，但按县分区会让数据库更容易维护。
我必须以这种方式工作，因为我正在处理犯罪记录，和
他们被县分开，有大量不同的文件，所以对于
我们很多人都要清理桌子并重装整个县
每次我得到更新。分区更容易...... :-)
表格大小可以是8000到300万条记录，具体取决于县内的
。
I know that, but partitioning by county makes the DB a lot easier to
maintain.
I have to work that way because I''m dealing with criminal records, and
they are separated by county with a ton of different files, so for
many of them I have to clear the table and reload the whole county
every time I get an update. Easier on partitions... :-)
Table size can be anywhere from 8000 to 3 million records, depending
on the county.
删除8000行是一件轻而易举的事，但删除300万行需要
一些资源，特别是如果行宽。但它仍然只有几分钟的时间。
Deleting 8000 rows is a breeze, but deleting 3 million rows takes
some resources, particularly if the rows are wide. But it still only
a matter of minutes.
但是，在尝试之前，我可以在一个分区中拥有3077个文件，然后删除/>
所有州的东西？
But, before trying, can I have 3077 files in ONE partition, and drop
all the states stuff?
不，在CREATE PARTITION FUNCTION的主题中，我发现你
的边界值不能超过999.
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro...ads/books.mspx
联机丛书for SQL Server 2000
http：// www .microsoft.com / sql / prodinf ... ons / books.mspx
No, in the topic for CREATE PARTITION FUNCTION, I found that you
cannot have more than 999 boundary values.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
这篇关于SQLServer表分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
