SQL 服务器分区
我有一个包含数百万条记录的表，我们正在考虑实施表分区。看看它，我们有一个外键“GroupID”，我们想根据它进行分区。这可能吗？
该组将添加更多条目，因此当添加新的 GroupID 时，可以动态创建分区吗？
I have a table that has millions of records and we are looking at implementing table partitioning. Looking at it we have a foreign key "GroupID" that we would like to partition on. Is this possible?
The Group will have more entries added to it, so as new GroupID's are added can the partition's be made dynamically?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
是的，您可以使用分区，是的，可以动态创建新分区。最简单的方法是使用不需要更新的分区方案。
SQL Enterprise 2005 和 SQL Enterprise 2005 up：
如果您使用的是 SQL 开发版或企业版，SQL 2005 及更高版本内置了对分区的透明支持。动态添加更多分区意味着您必须创建新的分区功能、新的分区方案以及可能的新文件组。
您可以使用 SPLIT 更改分区函数：
SQL 2000 或 SQL 2005 &非企业版：
否则，您仍然可以使用分区，但需要按照旧的 MS SQL 2000 方式进行。在旧的方式中，您必须物理创建许多表，然后创建一个视图，该视图是每个表的 UNION ALL。因此，您需要事先了解架构。
关于旧分区方式的好消息是动态创建新分区更加容易。您只需要创建一个具有新约束的新表并更新您的视图。
但有一些限制，分区列必须是主键的一部分。要直接插入到视图中，您不需要有 Identity 列（由 MS SQL 自动递增的列）
Yes you can use partitioning, yes new partitions can be made dynamically. It's easiest to use a partitioning scheme that does not need to be updated though.
SQL Enterprise 2005 & up:
SQL 2005 and up has built in transparent support for partitioning if you are using SQL development edition or enterprise edition. Dynamically adding more partitions means that you'd have to create a new partitioning function, new partition schemes and probably new file groups.
You can alter a partition function by using SPLIT:
SQL 2000 or SQL 2005 & up non-enterprise:
Otherwise you can still use partitioning but you need to do it the old MS SQL 2000 way. In the old way you have to physically create many tables, and then a view that is a UNION ALL of each of the tables. So you need knowledge of the schema before hand.
The good news about the old partitioning way is it's even easier to create new partitions dynamically. You just need to create a new table with a new constraint and update your view.
There are some limitations though, your partitioning column must be part of your primary key. And to do inserts directly into your View you'll need to NOT have an Identity column (one that is auto incremented by MS SQL)
Group ID 可能不是分区的最佳项，分区适合滚动窗口效应，更多的是为了删除过时的数据和滚动新数据而设计的。
通过按 ID 分组，您只允许自己添加/删除组，例如没有时间元素/数据寿命。每个分区表还有 1000 个分区的硬性限制，即最多 1000 个组。由于您打算添加组 ID，并且它没有指示为静态数字，因此您可能会达到此硬限制。
扩展 1000 个限制的唯一选择是跨多个分区表放置分区视图。
我想问的问题是，是什么导致添加新数据/删除旧数据，以及这是在什么基础上进行的。仅仅因为该表有数百万行，并不意味着它可以立即成为分区的候选者，这就是我问这个问题的原因。
就动态添加它们而言 - 该功能不存在，您必须编写存储过程和逻辑来处理下一个使用的新文件组/集的分配以及分区函数的拆分。这是可以做到的，但是 SQL 不会为你做这件事。
Group ID may not be the best item to partition on, partitioning suits a rolling window effect and is designed more for removing of outdated data and the rolling in of new data.
By Grouping on ID you only allow yourself to add / remove groups, e.g. no element of time / longevity of data. There is also a hard limit of 1000 partitions per parrtitioned table, limiting you to 1000 groups. Since you intend on add group ID's and it is not indicated as a static number you will potentially hit this hard limit.
The only option to extend that limit of 1000 is to place a partitioned view across multiple partitioned tables.
The question I would ask is what causes new data to be added / older data to be removed and on what basis is that made. Just because the table is millions of rows does not make it an immediate candidate for partitioning, which is why I would ask the question.
In terms of dynamically adding them - that feature does not exist you would have to write the stored procedures and logic to handle the allocation of new filegroups / set next used and splitting of the partition function. It can be done, but SQL is not going to do it for you.