SQL Server 2008 表分区
我有一个巨大的数据库，其中有几个表，可容纳数百万条记录。它为客户保存工程数据并不断增长。即使优化了索引，这也会影响性能。所以我一直在研究分区。
但是，我会考虑对表中保存的版本进行分区。该表最简单的形式包括：-
版本ID int
SheetID int
创建日期日期时间
SomeDate nvarchar(255)
版本整数
数据看起来像这样：
- 1, 1, 2010-09-01, 废话, 1
2, 1, 2010-09-02, 更废话, 2
3, 1, 2010-09-04, 废话, 3
4, 2, 2010-09-02, 更废话, 1
对于系统中“工作表”的每个新更改，此表都会添加一个带有新版本的新条目。理想情况下，我想对表进行分区，以便每个“工作表”都有前两个版本。因此，从上表中我想要版本 2 和 2。 3 表示工作表 id 2，版本 1 表示工作表 id 2，其余移动到分区。从我读到的来看，这似乎不可能。我是对还是错？
如果我错了，那么接下来我有一堆表，它们都链接回该表。它们保存输入数据的各种版本。我可以根据“主”表的分区对它们进行分区，还是分区必须专门基于它引用的表中的列？
注意，我不是最真实的 SQL 开发人员，所以如果这是一个完全愚蠢的问题，我深表歉意！
I have a huge database that has several tables that hold several million records. It's holding engineering data for a client and continually grows. This is impacting performance, even with optimised indexing. So I've been looking at partitioning.
However, I would be looking at partitioning on a version held in a table. In it's most simplistic form the table comprises of:-
VersionID int
SheetID int
CreationDate datetime
SomeDate nvarchar(255)
Version int
And the data would look something like:-
1, 1, 2010-09-01, blah, 1
2, 1, 2010-09-02, more blah, 2
3, 1, 2010-09-04, blah, 3
4, 2, 2010-09-02, more blah, 1
For every new change to a 'sheet' in the system, this table has a new entry added with a new version. Ideally I want to partition the table so that I have the top 2 versions for each 'sheet'. So from the table above I'd want versions 2 & 3 for sheet id 2, and version 1 for sheet id 2, with the rest moved to the partition. From what I've read this doesn't seem possible. Am I right or wrong?
If I'm wrong, then following on from this I have a bunch of tables that all link back to this table. These hold the various versions of data entered. Can I partition these based on the partition of the 'main' table, or does the partition have to be specifically based on a column from the table it refers to?
NB I'm not the most au fait SQL developer, so apologies if this is a completely daft question!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
对于“几百万条记录”的数量 - 分区会太过分 - 我怀疑您最好查看性能不佳的查询的查询计划并检查满足它们的索引。
分区的目标是提供一种在不进行大规模删除的情况下老化旧数据的方法，分区消除作为性能增益是副产品，并且仍然可以被合适的聚集索引超越 - 取决于索引大小/表大小。
你可以在SQL中进行范围分区，但是如果我判断需求正确的话，实现这样一个可变移动窗口的开销将是相当大的。
就最后一个问题而言，要对同一值进行分区，该值必须出现在两个表中，尽管从技术上讲，您可以对计算列进行分区，因此如果您有一个可用于计算相应的模式绑定函数值，那就足够了。 （但是，考虑到行数很少，我认为这有点过分了。）
For the quantity of 'several million records' - partitioning would be overkill - I suspect you would be better looking at the query plans of the poor performing queries and reviewing the indexes that are satisfying them.
The goal of partitioning is to provide a way of aging older data without large scale deletions, partition elimination as a performance gain is a by-product and can still be outpaced by a suitable clustered index - depending on the index size / table size.
You can do range partitioning within SQL, but it would be quite a lot of overhead to implement such a variable moving window, if I judge the requirements correctly.
In terms of the final question, to be partitioned on the same value, that value would have to appear in both tables, although technically you can partition on a computed column so if you had a schema bound function that could be used to compute the corresponding value, then that would suffice. (However, I think this is overkill, given the small number of rows.)