I have several million records in a table (book/media data) and I would like to partition the table using their ISBN numbers. However, I'd like to partition them by only using the last 3 digits of the numbers.
我在表格中有几百万条记录(书籍/媒体数据),我想用它们的ISBN号对表格进行分区。但是,我想通过仅使用数字的后3位数来对它们进行分区。
*000 *001 *002...*999
* 000 * 001 * 002 ... * 999
This divides up everything I want into neat, smaller/more usable sections of data since the books/media stored in there will be limited in numbers. So select/inserts will always (according to me) be relatively fast.
这将我想要的所有内容划分为整洁,更小/更有用的数据部分,因为存储在那里的书籍/媒体数量有限。因此,选择/插入总是(根据我)相对较快。
The problem now is, using the wizard, I can't figure out how to use a wildcard in the "Boundary" section when defining the function.
现在的问题是,使用向导,我无法弄清楚在定义函数时如何在“边界”部分使用通配符。
Is this possible?
这可能吗?
1 个解决方案
#1
2
You cannot. That would be hash partitioning and is not supported by SQL Server. What you can do instead is add a computed persisted column that retains the last 3 digits of the ISBN and partition by the persisted computed column.
你不能。这将是散列分区,SQL Server不支持。你可以做的是添加一个计算的持久列,它保留持久计算列的ISBN和分区的最后3位数。
As a side note, adding 1000 partitions to SQL Server 2008 R2 will make queries rather (very) slow on that table. Partitioning is never an option for better performance, the best result you can hope for is no regression from a non-partitioned table.
作为旁注,向SQL Server 2008 R2添加1000个分区将使该表上的查询相当(非常)缓慢。分区绝不是获得更好性能的选择,您可以期望的最佳结果是不会从非分区表中回归。
#1
2
You cannot. That would be hash partitioning and is not supported by SQL Server. What you can do instead is add a computed persisted column that retains the last 3 digits of the ISBN and partition by the persisted computed column.
你不能。这将是散列分区,SQL Server不支持。你可以做的是添加一个计算的持久列,它保留持久计算列的ISBN和分区的最后3位数。
As a side note, adding 1000 partitions to SQL Server 2008 R2 will make queries rather (very) slow on that table. Partitioning is never an option for better performance, the best result you can hope for is no regression from a non-partitioned table.
作为旁注,向SQL Server 2008 R2添加1000个分区将使该表上的查询相当(非常)缓慢。分区绝不是获得更好性能的选择,您可以期望的最佳结果是不会从非分区表中回归。